Case statements advice - query case field?

  • 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?

  • 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/

  • 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.

  • 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/

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

  • 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

  • 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

  • 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

  • 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

  • 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.

  • 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

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

  • 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

  • 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.

  • 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

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

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