SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Avoiding multiple sub selects
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bertie
Starting Member

7 Posts

Posted - 10/04/2012 :  18:17:13  Show Profile  Reply with Quote
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 CUSTOMER

Thanks for any help you can provide

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 10/04/2012 :  18:34:55  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 C
INNER JOIN CTE B ON C.Customer_Number=B.Customer_Number
Go to Top of Page

Bertie
Starting Member

7 Posts

Posted - 10/04/2012 :  19:03:08  Show Profile  Reply with Quote
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?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 10/04/2012 :  20:30:06  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000