Query Case Statement Filter based on Dependant Data

Question

Query Case Statement Filter based on Dependant Data

rjshupert

Mr or Mrs. 500

Points: 544
More actions
June 15, 2011 at 7:02 am

#242187

I have a table where I need to filter data based on two columns.
If the column 'Database_Status' has a value of 'deployed' then the case statement filter should end. However, if the 'Database_Status' value is NULL, then the filter needs to be on a column named 'Instance_Status'
So in the 'Instance_Status' column, I wrote this filter:
(SELECT CASE WHEN (SELECT a.Database_Status IS NOT NULL) THEN END ELSE (a.Instance_Status = N'deployed' OR IS NULL))
This statement gives me "Invalid or missing expression." I'm a newbie, not quite sure how to code this case statement. The key is if the 'Database_Status' value is NULL. In the future there could be other values other than deployed.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

calvo SSChampion Points: 12930 More actions · Answer 1

Table DDL (creation scripts) and sample data would be helpful.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

toddasd SSCertifiable Points: 6298 More actions · Answer 2

Sounds like this is what you are trying to accomplish

CASE WHEN ISNULL(Database_Status,'') = 'deployed' THEN Database_Status ELSE Instance_Status END

but I can't be sure without seeing the whole query.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

rjshupert Mr or Mrs. 500 Points: 544 More actions · Answer 3

So I'm selecting a bunch of data. If the value in 'Database_Status' is null the filter needs to shift to the 'Instance_Status' column. Which is why my thinking is/was along this line:

(SELECT CASE WHEN (SELECT a.Database_Status IS NOT NULL) THEN END ELSE (a.Instance_Status = N'deployed' OR IS NULL))

Here's the whole query:

SELECT Server, Instance, [Database], [User], OWNER_ID, FIRST_NAME, LAST_NAME, [Applied User/Group], [Membership Trace], Role, [Authentication Method],

[Account Type], [Account Status], [Password Blank], [Last Updated], IDTYPE, ID_DESCRIPTION, REPORTS_TO_PERSON_ID, REPORTSNAME,

PERSON_TYPE, PERSON_STATUS, DEPTNAME, DEPARTMENT_ID, BBU, Instance_Status, Database_Status, Instance_Environment,

Database_Environment, Instance_Infrastructure_Flag, Database_Infrastructure_Flag, Instance_SOX, Database_SOX

FROM dbo.V_SQL_Database_Inappr_Domain_User_Access_New AS a

WHERE (Database_Status = N'deployed' OR

Database_Status IS NULL) AND (Database_SOX = N'yes' OR

Database_SOX IS NULL)

toddasd SSCertifiable Points: 6298 More actions · Answer 4

If you want to switch on null, use this

WHERE

(COALESCE(Database_Status,Instance_Status) = N'deployed')

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

rjshupert Mr or Mrs. 500 Points: 544 More actions · Answer 5

So, forgive me I'm a new to some of these statements. It appears to me the COALESCE statement is looking at both. I only care about the 'Instance_Status' if the 'Database_Status' is NULL, and if I look at 'Instance_Status' deployed or NULL are acceptable values.

toddasd SSCertifiable Points: 6298 More actions · Answer 6

Coalesce returns the first non-null argument in the list. So this statement will compare Database_Status when it is non-null, but compare Instance_Status when Database_Status is null.

Check out http://msdn.microsoft.com/en-us/library/ms190349.aspx

You can also use it to compact

(Database_SOX = N'yes' OR Database_SOX IS NULL)

to this

(COALESCE(Database_SOX,N'yes') = N'yes')

Edit: cap key words

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

rjshupert Mr or Mrs. 500 Points: 544 More actions · Answer 7

So thanks for the sample. It was very close, I tweaked it to this:

(COALESCE(Database_Status,Instance_Status) = N'deployed' or is null)

And it's now displaying the expected results. Thanks so much.

toddasd SSCertifiable Points: 6298 More actions · Answer 8

rjshupert (6/15/2011)
So thanks for the sample. It was very close, I tweaked it to this:
(COALESCE(Database_Status,Instance_Status) = N'deployed' or is null)
And it's now displaying the expected results. Thanks so much.

So in this case, you could write

(COALESCE(Database_Status,Instance_Status,N'deployed') = N'deployed')

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

rjshupert Mr or Mrs. 500 Points: 544 More actions · Answer 9

I see, that is a much better solution. Thanks so much.