2

I have a large equation in a select statement that I need to reference in a where statement I think it is eating up my efficiency, I do not want to reference it more than I need to.

SELECT 'HeaderDetail' AS rowType, ... , [large CASE/WHEN statement] AS Tracking
...
WHERE NOT(@DNC IN([large CASE/WHEN statement]))
AND [large CASE/WHEN statement] IS NOT NULL

I feel like each time I call the statement it would need to recalculate the values and the query already takes a few seconds to run. Is there a way to reference the statement without having it to run the sub query multiple times?

Thanks in advance for the help!

Robert Kirkpatrick
  • 23
  • 2

3 Answers3

1

What happens when you filter the rows in outer query or CTE.

With CTE as 
(
SELECT 'HeaderDetail' AS rowType, ... , [large CASE/WHEN statement] AS Tracking
...
)
select * from CTE
WHERE NOT(@DNC IN(Tracking))
AND Tracking IS NOT NULL

or use Sub-Select

select * from 
(
SELECT 'HeaderDetail' AS rowType, ... , [large CASE/WHEN statement] AS Tracking
...
) AS A
WHERE NOT(@DNC IN(Tracking))
AND Tracking IS NOT NULL
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

use a subquery

select *
from (
your query with the large case statement goes here
) temp
where theAliasForThatCaseStatment is not null
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
0

Give this a try:

SELECT 'HeaderDetail' AS rowType, ... , x.Tracking
FROM mytable
CROSS APPLY (SELECT [large CASE/WHEN statement]) x(Tracking)
WHERE NOT(@DNC IN(x.Tracking)) AND x.Tracking IS NOT NULL

As a side note, CASE is an expression that returns just a single value. So be cautious when using it with IN operator.

SQL Fiddle Demo

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • Ended up using a subquery. I use the case statement to return data as a CSV string so I should be able to apply IN (I hope). Still working on the rest of the code. Thanks for the reply. – Robert Kirkpatrick May 11 '15 at 15:36