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

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

Open in new window

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

LVL 35

Paul MacDonaldConnect With a Mentor Director, Information SystemsCommented: 2011-03-16

You want the ISNULL command:
ISNULL ( check_expression , replacement_value )

so

...
ISNULL(bit.TPM_Activated0, "False") AS 'TPM Activated'
...

LVL 24

jimyXCommented: 2011-03-16

You can use (IsNull(Field, 'False') for all those fields that you do not want them to show NULL.

jimyXConnect With a Mentor Commented: 2011-03-16

So your query becomes:

LVL 61

HainKurtSr. System AnalystCommented: 2011-03-16

you can use case statement like this

case
when bit.TPM_Activated0 is null then 'False'
else bit.TPM_Activated0
end

LorrecAuthor Commented: 2011-03-16

I used a combination of both replies. Thank you very much for the quick response.

Modify output results of SQL 2005 query

Who is Participating?

Already a member? Login.