Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Case statements advice - query case field? Expand / Collapse
Author
Message
Posted Monday, August 12, 2013 8:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 25, 2014 5:40 AM
Points: 47, Visits: 183
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?
Post #1483347
Posted Monday, August 12, 2013 8:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:15 PM
Points: 13,077, Visits: 12,523
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1483356
Posted Monday, August 12, 2013 8:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 25, 2014 5:40 AM
Points: 47, Visits: 183
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.
Post #1483363
Posted Monday, August 12, 2013 9:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:15 PM
Points: 13,077, Visits: 12,523
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1483365
Posted Monday, August 12, 2013 9:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 25, 2014 5:40 AM
Points: 47, Visits: 183
SSRS doesn't like that method, never mind at least I have it working. Many thanks Sean.
Post #1483370
Posted Monday, August 12, 2013 9:50 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:06 AM
Points: 612, Visits: 2,853
Instead of the case statement you can also do this:

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



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1483378
Posted Monday, August 12, 2013 7:06 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1483525
Posted Monday, August 12, 2013 7:15 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:06 AM
Points: 612, Visits: 2,853
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


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1483528
Posted Monday, August 12, 2013 7:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1483532
Posted Tuesday, August 13, 2013 2:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 25, 2014 5:40 AM
Points: 47, Visits: 183
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.
Post #1483615
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse