Operand data type bit is invalid for max operator

1

I have the following SQL Query:

SELECT
MAX(TABLE2.System_OU_Name0) AS [DEVICE_AD_OU],
MAX(TABLE1.AD_Site_Name0) AS [AD_SITE_NAME],
MAX(TABLE1.Netbios_Name0) AS [DEVICE_NAME],
MAX(TABLE1.User_Name0) AS [DEVICE_USER_ID],
MAX(TABLE1.Last_Logon_Timestamp0) AS [DEVICE_LAST_LOGON],
MAX(TABLE1.Is_Assigned_To_User0) AS [DEVICE_ASSIGNED_USER],
MAX(TABLE1.Is_Virtual_Machine0) AS [DEVICE_VIRTUAL_MACHINE],
MAX(TABLE1.User_Domain0) AS [DEVICE_DOMAIN],
MAX(TABLE1.Active0) AS [DEVICE_ACTIVE_RESOURCE],
MAX(TABLE6.Manufacturer0) AS [DEVICE_MANUFACTURER],
MAX(TABLE6.Model0) AS [DEVICE_MODEL],
MAX(TABLE1.SerialNumber) AS [DEVICE_SERIAL_NUMBER],
MAX(TABLE8.IP_Addresses0) AS [DEVICE_IP_ADDRESS],
MAX(TABLE9.MAC_Addresses0) AS [DEVICE_MAC_ADDRESS],
MAX(TABLE10.ChassisTypes0) AS [DEVICE_CHASSIS_TYPE],
MAX(TABLE5.Name0) AS [DEVICE_BIOS_VERSION],
MAX(TABLE3.TotalVisibleMemorySize0) AS [DEVICE_MEMORY (MB)],
MAX(TABLE7.Caption0) AS [DEVICE_HD_MODEL],
MAX(TABLE7.Size0) AS [DEVICE_HD_SIZE (MB)],
MAX(TABLE3.Caption0) AS [DEVICE_OS_NAME],
MAX(TABLE3.Version0) AS [DEVICE_OS_VERSION],
MAX(Table3.CSDVersion0) AS [DEVICE_OS_CSD_VERSION],
MAX(TABLE11.SystemType0) AS [DEVICE_SYSTEM_TYPE],
MAX(TABLE1.CPUType0) AS [DEVICE PROCESSOR],
MAX(TABLE6.NumberOfProcessors0) AS [DEVICE_CPU_COUNT],
MAX(TABLE4.MaxClockSpeed0) AS [DEVICE_CPU_SPEED],
MAX(TABLE1.Creation_Date0) AS [DEVICE_CREATION_DATE],
MAX(TABLE1.Client_Version0) AS [SCCM_CLIENT_VERSION],
MAX(TABLE1.User_Account_Control0) AS [DEVICE_USER_ACCOUNT_CONTROL],
MAX(TABLE12.ProtectionStatus0) AS [DEVICE_BITLOCKER_STATUS],
MAX(TABLE1.ResourceID) AS [DEVICE_SCCM_ID]

FROM dbo.[v_R_System] AS TABLE1 INNER JOIN dbo.[v_RA_System_SystemOUName] AS TABLE2
ON TABLE1.ResourceID = TABLE2.ResourceID
JOIN dbo.v_GS_OPERATING_SYSTEM AS TABLE3 ON TABLE1.ResourceID = TABLE3.ResourceID
JOIN dbo.v_GS_PROCESSOR AS TABLE4 ON TABLE1.ResourceID = TABLE4.ResourceID
JOIN dbo.v_GS_PC_BIOS AS TABLE5 ON TABLE1.ResourceID = TABLE5.ResourceID
JOIN dbo.v_GS_COMPUTER_SYSTEM AS TABLE6 ON TABLE1.ResourceID = TABLE6.ResourceID
JOIN dbo.v_GS_DISK AS TABLE7 ON TABLE1.ResourceID = TABLE7.ResourceID
JOIN dbo.v_RA_System_IPAddresses AS TABLE8 ON TABLE1.ResourceID = TABLE8.ResourceID
JOIN dbo.v_RA_System_MACAddresses AS TABLE9 ON TABLE1.ResourceID = TABLE9.ResourceID
JOIN dbo.v_GS_SYSTEM_ENCLOSURE AS TABLE10 ON TABLE1.ResourceID = TABLE10.ResourceID
JOIN dbo.v_HS_Computer_System AS TABLE11 ON TABLE1.ResourceID = TABLE11.ResourceID
JOIN dbo.v_GS_Encryptable_Volume AS TABLE12 ON TABLE1.ResourceID = TABLE12.ResourceID
WHERE Netbios_Name0 LIKE '%' AND TABLE7.DeviceID0 = '\.\PHYSICALDRIVE0'
GROUP BY
TABLE1.Netbios_Name0

When executing it, I receive the following error:

Msg 8117, Level 16, State 1, Line 8
Operand data type bit is invalid for max operator.

When looking at some of the fields for that line in the Database they come up as NULL. Any suggestions to adjust my query.

2

One of those columns is data type boolean.

3

You'll need to convert the bit to a tinyint: SQL doesn't allow aggregate functions against bit columns.

Most likely it's these columns:
...
MAX(CAST(TABLE1.Active0 AS tinyint)) AS [DEVICE_ACTIVE_RESOURCE],
...
MAX(CAST(TABLE12.ProtectionStatus0 AS tinyint)) AS [DEVICE_BITLOCKER_STATUS],
...

4

Tried that and the same error popped up

5

Then you have additional column(s) that is(are) bits. Find and CAST it(them) to tinyint also.

6

I found the additional columns and there query executes but no data is displayed

7

How many rows do you get when you run this?

select *
FROM dbo.[v_R_System] AS TABLE1 
INNER JOIN dbo.[v_RA_System_SystemOUName] AS TABLE2
ON TABLE1.ResourceID = TABLE2.ResourceID
JOIN dbo.v_GS_OPERATING_SYSTEM AS TABLE3 
ON TABLE1.ResourceID = TABLE3.ResourceID
JOIN dbo.v_GS_PROCESSOR AS TABLE4 
ON TABLE1.ResourceID = TABLE4.ResourceID
JOIN dbo.v_GS_PC_BIOS AS TABLE5 
ON TABLE1.ResourceID = TABLE5.ResourceID
JOIN dbo.v_GS_COMPUTER_SYSTEM AS TABLE6 
ON TABLE1.ResourceID = TABLE6.ResourceID
JOIN dbo.v_GS_DISK AS TABLE7 
ON TABLE1.ResourceID = TABLE7.ResourceID
JOIN dbo.v_RA_System_IPAddresses AS TABLE8 
ON TABLE1.ResourceID = TABLE8.ResourceID
JOIN dbo.v_RA_System_MACAddresses AS TABLE9 
ON TABLE1.ResourceID = TABLE9.ResourceID
JOIN dbo.v_GS_SYSTEM_ENCLOSURE AS TABLE10 
ON TABLE1.ResourceID = TABLE10.ResourceID
JOIN dbo.v_HS_Computer_System AS TABLE11 
ON TABLE1.ResourceID = TABLE11.ResourceID
JOIN dbo.v_GS_Encryptable_Volume AS TABLE12 
ON TABLE1.ResourceID = TABLE12.ResourceID
WHERE Netbios_Name0 LIKE '%' 
AND TABLE7.DeviceID0 = '\.\PHYSICALDRIVE0'
8

Fixed my issue. One of my columns was causing the issue, but it may not be required. Thanks all for the help.