Wildcard to show null values?

Question

Post reply

Wildcard to show null values?

shujaahmad2004

Old Hand

Points: 348
More actions
April 9, 2010 at 8:03 am

#220363

Hi guys,
I have a query in one of my reporting services reports which is based on a case statement that uses a wildcard. (e.g. HAVING DIRECTORATE_CODE LIKE CASE WHEN @Directorate = 'ALL' THEN '%%' ELSE @Directorate END).
This code is supposed to show all records for directorate when the 'ALL' option is selected from the report filter combo box. This works fine BUT it is currently excluding null values. I'm assuming that the % wildcard ignores nulls.
Is there anyway i can get the case statement (or more specifically the 'THEN' statement) to show all records including the null values when 'ALL' is seleceted?
Regards,
Shuja

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Try

HAVING DIRECTORATE_CODE LIKE CASE WHEN @Directorate = 'ALL' THEN DIRECTORATE_CODE ELSE @Directorate END)

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

shujaahmad2004 Old Hand Points: 348 More actions · Answer 2

Hi Jason,

I tried your solution but i get the same record count and the null value records aren't showing.

I also tried to enclose the DIRECTORATE_CODE in wildcards ('%' + DIRCETORATE_CODE + '%') but this did the same thing as your solution. I'm getting the same results from all of these methods - no null value records showing.

Any other ideas?

Shuja

shujaahmad2004 Old Hand Points: 348 More actions · Answer 3

I think i might have found the solution after fiddling with Jason's original solution.

I've added a COALESCE to the DIRECTORATE_CODE at both end s of the case statement so that null values are compared as text values.

(e.g. HAVING (COALESCE(DIRECTORATE_CODE,'NULL') LIKE CASE WHEN @Directorate= 'ALL' THEN COALESCE(DIRECTORATE_CODE,'NULL') ELSE 'ALL' END)

shujaahmad2004 Old Hand Points: 348 More actions · Answer 4

shujaahmad2004

Old Hand

Points: 348

April 9, 2010 at 8:37 am

#1149165

Thanks for your help Jason.

Regards, Shuja

david_wendelken SSCrazy Points: 2388 More actions · Answer 5

shujaahmad2004 (4/9/2010)
Hi guys,
I have a query in one of my reporting services reports which is based on a case statement that uses a wildcard. (e.g. HAVING DIRECTORATE_CODE LIKE CASE WHEN @Directorate = 'ALL' THEN '%%' ELSE @Directorate END).
This code is supposed to show all records for directorate when the 'ALL' option is selected from the report filter combo box. This works fine BUT it is currently excluding null values. I'm assuming that the % wildcard ignores nulls.
Is there anyway i can get the case statement (or more specifically the 'THEN' statement) to show all records including the null values when 'ALL' is seleceted?
Regards,
Shuja

this is simpler:

(@directorate = 'ALL' or directorate_code like @directorate)