Modify output results of SQL 2005 query
Hello. My environment is MS SQL 2005 SP3. I am trying to figure out a way to modify the results of a query. My query works correctly (query below). However, I would like to display anything with a null value in BitLocker_Enabled0, TPM_On0 and TPM_Activated0 as False. Currently, it says null when there is no value.
Would Case When give me the results? So far, I cannot get it to work properly.
Right now, the results look like the below.
US FDS22 user1 Latitude E6400 NULL NULL NULL
US FDS24 user2 Latitude E6400 NULL NULL NULL
US FDS25 user15 Latitude E6410 True True True
I want it to look like
US FDS22 user1 Latitude E6400 False False False
US FDS24 user2 Latitude E6400 False False False
US FDS25 user15 Latitude E6410 True True True
Would Case When give me the results? So far, I cannot get it to work properly.
Right now, the results look like the below.
US FDS22 user1 Latitude E6400 NULL NULL NULL
US FDS24 user2 Latitude E6400 NULL NULL NULL
US FDS25 user15 Latitude E6410 True True True
I want it to look like
US FDS22 user1 Latitude E6400 False False False
US FDS24 user2 Latitude E6400 False False False
US FDS25 user15 Latitude E6410 True True True
SELECT sys.AD_Site_Name0, sys.Name0, sys.User_Name0, cs.model0 AS Model, bit.BitLocker_Enabled0 AS 'BitLocker Enabled', bit.TPM_On0 AS 'TPM On',
bit.TPM_Activated0 AS 'TPM Activated', bit.Script_Last_Run0 AS 'Last Time Script was Run'
FROM v_GS_BitLocker_TPM_Info0 AS bit INNER JOIN
v_R_System AS sys ON bit.ResourceID = sys.ResourceID INNER JOIN
v_GS_COMPUTER_SYSTEM as cs ON sys.ResourceID = cs.ResourceID
WHERE (sys.Operating_System_Name_and0 = 'Microsoft Windows NT Workstation 6.1') AND (cs.Model0 LIKE '%Latitude%') OR
(sys.Operating_System_Name_and0 = 'Microsoft Windows NT Workstation 6.1') AND (cs.Model0 LIKE '%Precision M%')
ORDER BY sys.AD_Site_Name0
Who is Participating?
So your query becomes:
SELECT sys.AD_Site_Name0, sys.Name0, sys.User_Name0, cs.model0 AS Model, (IsNull(bit.BitLocker_Enabled0, 'False') AS 'BitLocker Enabled', (IsNull(bit.TPM_On0, 'False') AS 'TPM On',
(IsNull(bit.TPM_Activated0, 'False') AS 'TPM Activated', bit.Script_Last_Run0 AS 'Last Time Script was Run'
FROM v_GS_BitLocker_TPM_Info0 AS bit INNER JOIN
v_R_System AS sys ON bit.ResourceID = sys.ResourceID INNER JOIN
v_GS_COMPUTER_SYSTEM as cs ON sys.ResourceID = cs.ResourceID
WHERE (sys.Operating_System_Name_and0 = 'Microsoft Windows NT Workstation 6.1') AND (cs.Model0 LIKE '%Latitude%') OR
(sys.Operating_System_Name_and0 = 'Microsoft Windows NT Workstation 6.1') AND (cs.Model0 LIKE '%Precision M%')
ORDER BY sys.AD_Site_Name0
you can use case statement like this
case
when bit.TPM_Activated0 is null then 'False'
else bit.TPM_Activated0
end
case
when bit.TPM_Activated0 is null then 'False'
else bit.TPM_Activated0
end
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
ISNULL ( check_expression , replacement_value )
so
...
ISNULL(bit.TPM_Activated0,
...