Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bertie
Starting Member
7 Posts |
 Posted - 2012-10-04 : 18:17:13
|
| Hello,This could be blindingly obvious, but if so please let me know.I'm running a case statement on a subselect, however when I'm writing it I'm having to repeat the subselect, and even more so if I decide to put a where / having clause later on. Is there a way to store the result of the subselect to avoid duplication (without the use of temporary tables if possible)? Here's a very over simplified example, and I know in this one I could do it without the subselect! You'll see on lines 3 and 4 where I have the duplication I'm trying to avoid.SELECT customer_number,CASE WHEN (select sum(invoice_values) from invoice where invoice.customer_number = customer.customer_number) > 1000 THEN (select sum(invoice_values) from invoice where invoice.customer_number = customer.customer_number) ELSE 'Low value' END as 'Customer Performance'FROM CUSTOMERThanks for any help you can provide |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-04 : 18:34:55
|
| WITH CTE(Customer_Number, Total) AS (SELECT Customer_Number, SUM(invoice_values) FROM invoice GROUP BY Customer_Number)SELECT C.Customer_Number, CASE WHEN B.Total>1000 THEN CAST(B.Total as varchar)ELSE 'Low value' END AS [Customer Performance]FROM CUSTOMER CINNER JOIN CTE B ON C.Customer_Number=B.Customer_Number |
 |
|
|
Bertie
Starting Member
7 Posts |
Posted - 2012-10-04 : 19:03:08
|
| Thanks, that works!In terms of efficiency do you know if the WITH just runs the sub query once then stores the results, or will it be run again every time I use it in the rest of the query? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-04 : 20:30:06
|
| You'd have to look at the execution plan to confirm it, but common table expressions are typically executed only once. It's possible your subqueries are also only executed once, it depends on the optimizer. |
|
|
|
|
|
|
|
|
Avoiding multiple sub selects