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 Tuesday, August 13, 2013 4:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 6,858, Visits: 14,148
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1483631
Posted Tuesday, August 13, 2013 4:13 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
That would have been great, really great - had it been supported on my version of SSRS....

Post #1483634
Posted Tuesday, August 13, 2013 4:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 6,858, Visits: 14,148
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1483635
Posted Tuesday, August 13, 2013 4:36 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
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.
Post #1483641
Posted Tuesday, August 13, 2013 7:11 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 632, Visits: 2,948
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

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.


-- 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 #1483714
Posted Tuesday, August 13, 2013 7:16 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
Alan.B (8/13/2013)
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

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.


These are the things I can only learn on here - I'll remember that for the future.

Post #1483718
Posted Tuesday, August 13, 2013 8:08 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 968, Visits: 3,000
Alan.B (8/13/2013)
Stupid Microsoft





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1483755
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse