SQL Reporting : An item with the same key has already been added

Question

I keep getting this error on SQL Build. I try to execute the query and it displays the right outcome but when I try to refresh fields, I get an error saying:

An item with the same key has already been added.

This is my previous SQL report that would display SCCM agents in our environment and that worked.

Select Distinct
	vrs.AD_Site_Name0 [AD Site],  
	vrs.name0 [Computer], 
	vrs.User_Name0 [Current User], 
	vru.Unique_User_Name0 [Account Name], 
	vru.Full_User_Name0 [Display Name], 
	opsys.caption0 [OS], 
	vrs.Client0 [Client],
	
	CASE
	When compsys.SystemType0 like '%x86%' Then 'x86'
	When compsys.SystemType0 like '%x64%' Then 'x64'
	Else 'unknown'
END as [System Type],

CASE 	
	WHEN vrs.Name0 like 'Ou%' THEN	'Ou' 
	
	ELSE 'Others'
END as [AD Site Name],

CASE 
		WHEN opsys.Caption0 like '%Windows 7%' THEN 'Windows 7'
		WHEN opsys.Caption0 like '%Windows 8%' Then 'Windows 8'
		WHEN opsys.Caption0 like '%Windows 10%' THEN 'Windows 10'
		When opsys.Caption0 like '%Windows Server 2008%' Then 'Windows Server 2008'
		When opsys.Caption0 like '%Windows Server 2012%' Then 'Windows Server 2012'
		Else opsys.Caption0 
	END as [OS],
	CASE 
	WHEN opsys.CSDVersion0 is null THEN 'No SP'
	Else opsys.CSDVersion0
END as [Service Pack],
	vrs.Client_Version0 AS "Agent Version"	
	FROM
	v_R_System vrs
	LEFT Join v_R_User vru ON vrs.User_Name0 = vru.User_Name0
	Left Join v_GS_OPERATING_SYSTEM OPSYS on vrs.ResourceID = opsys.ResourceID
	Left Join v_GS_Computer_System compsys on vrs.ResourceID = compsys.ResourceID
	
	 
	Inner Join v_FullCollectionMembership FCM ON vrs.ResourceID = FCM.ResourceID AND FCM.CollectionID = 'xxxx'
WHERE 
	
	vrs.Name0 not like 'unknown' 
ORDER BY
	vrs.name0

So I need to add the LastHWScan and LastSWScan to the report and I tried this which works for my other reports but I get the error, "An item with the same key has already been added". Maybe I'm just not seeing it. Hopefully, someone can assist me with this.

Select distinct
vrs.AD_Site_Name0[AD Site],
  vrs.name0[Computer],
  vrs.User_Name0[Current User],
  vru.Unique_User_Name0[Account Name],
  vru.Full_User_Name0[Display Name],
  opsys.caption0[OS],
  vrs.Client0[Client],
  HWSCAN.LastHWScan[LastHWScan],
  SWSCAN.LastScanDate[LastSWScan],

  CASE
When compsys.SystemType0 like '%x86%'
Then 'x86'
When compsys.SystemType0 like '%x64%'
Then 'x64'
Else 'unknown'
END as[System Type],

  CASE
WHEN vrs.Name0 like 'OU%'
THEN 'OU'

ELSE 'Others'
END as[AD Site Name],

  CASE
WHEN opsys.Caption0 like '%Windows 7%'
THEN 'Windows 7'
WHEN opsys.Caption0 like '%Windows 8%'
Then 'Windows 8'
WHEN opsys.Caption0 like '%Windows 10%'
THEN 'Windows 10'
When opsys.Caption0 like '%Windows Server 2008%'
Then 'Windows Server 2008'
When opsys.Caption0 like '%Windows Server 2012%'
Then 'Windows Server 2012'
Else opsys.Caption0
END as[OS],
  CASE
WHEN opsys.CSDVersion0 is null THEN 'No SP'
Else opsys.CSDVersion0
END as[Service Pack],
  vrs.Client_Version0 AS "Agent Version"
FROM
v_R_System vrs
LEFT Join v_R_User vru ON vrs.User_Name0 = vru.User_Name0
Left Join v_GS_OPERATING_SYSTEM OPSYS on vrs.ResourceID = opsys.ResourceID
Left Join v_GS_Computer_System compsys on vrs.ResourceID = compsys.ResourceID
Left join v_GS_WORKSTATION_STATUS HWSCAN on vrs.resourceID = HWSCAN.resourceID
Left join v_GS_LastSoftwareScan SWSCAN on vrs.resourceID = SWSCAN.resourceID

Inner Join v_FullCollectionMembership FCM ON vrs.ResourceID = FCM.ResourceID AND FCM.CollectionID = 'xxx'
WHERE

vrs.Name0 not like 'unknown'
ORDER BY
vrs.name0

Answer 1

While SQL allows multiple columns to have the same name the Report Designer does not. This is the reason why you can preview the query but not save.

So you cannot have

opsys.caption0 [OS]

directly as well as the case where you prettify the os name

  CASE
WHEN opsys.Caption0 like '%Windows 7%'
THEN 'Windows 7'
WHEN opsys.Caption0 like '%Windows 8%'
Then 'Windows 8'
WHEN opsys.Caption0 like '%Windows 10%'
THEN 'Windows 10'
When opsys.Caption0 like '%Windows Server 2008%'
Then 'Windows Server 2008'
When opsys.Caption0 like '%Windows Server 2012%'
Then 'Windows Server 2012'
Else opsys.Caption0
END as[OS]

If you really need both you have to rename one of the columns to a unique name.

I don't know why this would have worked with the earlier example but it shouldn't imo. The changes themselves should be fine. In generally with this error it's easiest to just run it in the preview (or in sql studio) and check for multiple columns with the same name.