Case statements advice - query case field?

Question

Post reply

Case statements advice - query case field?

learning_sql

Right there with Babe

Points: 727
More actions
August 12, 2013 at 8:25 am

#281836

Hi,
So basically I have three tables with three types of "things". Each of these tables have a column called "name". My fourth table contains references to these tables through an id field, only one of these tables will be related to the fourth table - I have produced the following SQL so the query has a name column which is populated by the related tables name.
SELECT
CASE
WHEN thing1.name IS NOT NULL THEN thing1.name
WHEN thing2.name IS NOT NULL THEN thing2.name
WHEN thing3.name IS NOT NULL THEN thing3.name
END AS name,
base.ls_id,
base.date_lease_start,
base.date_lease_end,
base.lease_term,
base.amount_current_rent
FROM base_table AS base
LEFT OUTER JOIN thing1_table AS thing1 ON base.as_id = thing1.as_id
LEFT OUTER JOIN thing2_table AS thing2 ON base.bl_id = thing2.bl_id
LEFT OUTER JOIN thing3_table AS thing3 ON base.ld_id = thing3.ld_id
That query works, my problem is the following, I hope to use the query in a SSRS report with a parameter enabling a search against the name column (which the case statement populates).
I would like to query the returned "name" column from that query - I could populate a temporary table however I would like to know what the "proper" way is for this?

Viewing 15 posts - 1 through 15 (of 16 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

No need for a temp table here. Just add a where clause.

where thing1.name = @SearchVal

OR thing2.name = @SearchVal

OR thing3.name = @SearchVal

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

learning_sql Right there with Babe Points: 727 More actions · Answer 2

thanks, I was hoping for an easy answer and got it.

So there is no way to query the case's result itself, make sense but I wanted to be sure.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

learning_sql (8/12/2013)
thanks, I was hoping for an easy answer and got it.
So there is no way to query the case's result itself, make sense but I wanted to be sure.

Of course there is a way. You could do something like this.

select * from

(

SELECT

CASE

WHEN thing1.name IS NOT NULL THEN thing1.name

WHEN thing2.name IS NOT NULL THEN thing2.name

WHEN thing3.name IS NOT NULL THEN thing3.name

END AS name,

base.ls_id,

base.date_lease_start,

base.date_lease_end,

base.lease_term,

base.amount_current_rent

FROM base_table AS base

LEFT OUTER JOIN thing1_table AS thing1 ON base.as_id = thing1.as_id

LEFT OUTER JOIN thing2_table AS thing2 ON base.bl_id = thing2.bl_id

LEFT OUTER JOIN thing3_table AS thing3 ON base.ld_id = thing3.ld_id

) x

where x.name = @SearchVal

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

learning_sql Right there with Babe Points: 727 More actions · Answer 4

SSRS doesn't like that method, never mind at least I have it working. Many thanks Sean.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

Instead of the case statement you can also do this:

SELECT ISNULL(thing1.name,(ISNULL(thing2.name,thing3.name))) AS name

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Dwain Camps SSC Guru Points: 86978 More actions · Answer 6

Alan.B (8/12/2013)
Instead of the case statement you can also do this:
SELECT ISNULL(thing1.name,(ISNULL(thing2.name,thing3.name))) AS name

And why not:

SELECT COALESCE(thing1.name,thing2.name,thing3.name) AS name

?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Alan Burstein SSC Guru Points: 61141 More actions · Answer 7

dwain.c (8/12/2013)
Alan.B (8/12/2013)
Instead of the case statement you can also do this:
SELECT ISNULL(thing1.name,(ISNULL(thing2.name,thing3.name))) AS name
And why not:
SELECT COALESCE(thing1.name,thing2.name,thing3.name) AS name
?

Nice. That is cleaner and easier to read.

+1

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Dwain Camps SSC Guru Points: 86978 More actions · Answer 8

Alan.B (8/12/2013)
dwain.c (8/12/2013)
Alan.B (8/12/2013)
Instead of the case statement you can also do this:
SELECT ISNULL(thing1.name,(ISNULL(thing2.name,thing3.name))) AS name
And why not:
SELECT COALESCE(thing1.name,thing2.name,thing3.name) AS name
?
Nice. That is cleaner and easier to read.
+1

I confess I don't use COALESCE myself very much but it is nice to know it is there for cases like this.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

learning_sql Right there with Babe Points: 727 More actions · Answer 9

Thanks, those are much easier to read.

I have tested against SSRS, I will be using the coalesce query alongside the usual where clauses.

Thanks again.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

SELECT

x.Name,

base.ls_id,

base.date_lease_start,

base.date_lease_end,

base.lease_term,

base.amount_current_rent

FROM base_table AS base

LEFT OUTER JOIN thing1_table AS thing1 ON base.as_id = thing1.as_id

LEFT OUTER JOIN thing2_table AS thing2 ON base.bl_id = thing2.bl_id

LEFT OUTER JOIN thing3_table AS thing3 ON base.ld_id = thing3.ld_id

CROSS APPLY (SELECT Name = COALESCE(thing1.name, thing2.name, thing3.name)) x

WHERE x.name = @SearchVal

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

learning_sql Right there with Babe Points: 727 More actions · Answer 11

That would have been great, really great - had it been supported on my version of SSRS....:(

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Really? It's just a query. What error message does RS fart out?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

learning_sql Right there with Babe Points: 727 More actions · Answer 13

Ha, it literally said "Reporting Services does not support CROSS APPLY".

However I have just been playing around and when just typing the query it worked every time, I have now published the report and it is still working...maybe I made the message up.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 14

learning_sql (8/13/2013)
Ha, it literally said "Reporting Services does not support CROSS APPLY".
However I have just been playing around and when just typing the query it worked every time, I have now published the report and it is still working...maybe I made the message up.

Correct me if I am wrong but: you are putting this query in an SSRS dataset as text (ad hoc query). You need to put the query into a stored proc and then call the stored proc from the dataset. Other T-SQL commands that you can't use in an SSRS ad hoc query include INTERSECT and EXCEPT. Stupid Microsoft :crazy:

Anyhow, I would recommend always using stored procedures in your SSRS datasets. You will see much better performance, your code will be more manageable and re-usable.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001