Select with subquery to avoid starting more than once?

Question

I have a request like this

Select Col_A ,Col_B ,Col_C ,CASE WHEN (SELECT ...{a very complicated query}...) IS NOT NULL THEN 1 ELSE 0 END CASE AS Col_D FROM MyTable 

The subquery that Col_D produces returns an integer if it is not null.

I need to show BIT that Col_D is, but I also need to show INT which it returns. Usually I just rewrite the subquery and call it Col_E, but given its complexity, I don’t want to run it twice. Ideally, I would:

 Select Col_A ,Col_B ,Col_C ,(SELECT ...{a very complicated query}...) AS Col_E ,CASE WHEN Col_E IS NOT NULL THEN 1 ELSE 0 END CASE AS Col_D FROM MyTable 

Do I have any options? (MS SQL 2008)

EDIT: Sorry - I should have mentioned that a complex query includes a where clause based on my columns ie

 SELECT ...{a very complicated query}... WHERE X = Col_A AND Y = Col_B 

Answer 1

So, if the results of your query are just one value or null, you can use:

 Select Col_A ,Col_B ,Col_C ,CASE WHEN t.whatevercol IS NOT NULL THEN 1 ELSE 0 END CASE AS Col_D, t.whatevercol FROM MyTable, (SELECT ...{a very complicated query}...) t 

Be careful, as this can lead to a Cartesian product, although if there is potential multiple returns. If there are fields you can join to, this might be a better approach (and since you are checking for NULL, you might need an EXTERNAL JOIN.)

Answer 2

You can Col_E query that returns Col_E in another SELECT , and add Col_D on top of it, for example:

 SELECT Col_A ,Col_B ,Col_C ,Col_E , CASE WHEN Col_E IS NOT NULL THEN 1 ELSE 0 END CASE AS Col_D FROM ( Select Col_A ,Col_B ,Col_C ,(SELECT ...{a very complicated query}...) AS Col_E FROM MyTable ) X 

This way you won’t need to copy the query because its result will be available to an external SELECT .