Solved

Modify output results of SQL 2005 query

Posted on 2011-03-16
5
318 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Lorrec
5 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 35149129
You can use (IsNull(Field, 'False') for all those fields that you do not want them to show NULL.
0
 
LVL 34

Accepted Solution

by:
Paul MacDonald earned 250 total points
ID: 35149136
You want the ISNULL  command:
     ISNULL ( check_expression , replacement_value )

so

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

Assisted Solution

by:jimyX
jimyX earned 250 total points
ID: 35149158
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

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 35149170
you can use case statement like this

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

Author Closing Comment

by:Lorrec
ID: 35149380
I used a combination of both replies. Thank you very much for the quick response.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Modifying SQL 2008/2012 PARTITIONS 3 55
Insert statement is inserting duplicate records 15 58
Getting the 3 middle digits 4 35
SQL Select - Finding chars in a column 2 56
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now