CODEWITHSUNDEEP
sqlsql-server
Warren
Warren

Reputation: 2014

Select with subquery avoid running more than once?

I have a query 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 produces Col_D returns an integer when it is not null.

I need to show the BIT that Col_D is, but I also need to show the INT that it returns. Normally, I would just rewrite the subquery and call it Col_E, but given it's complexity, I don't really want to run it twice. Ideally, I would have:

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 the complicated query includes a where clause based on my columns i.e.

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

Upvotes: 4

Views: 1827

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726539

You can nest the query that returns Col_E in another SELECT, and add Col_D on top of it, like this:

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 would not need to copy the query, because its result would be available to the outer SELECT.

Upvotes: 2

sgeddes
sgeddes

Reputation: 62841

So if the results of your query are only a single value or null, you could 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

Do be careful as this could result in a cartesian product though if there are potential multiple returns. If there are fields you can join on, that might be a better approach (and since you're checking for NULL, you may need an OUTER JOIN).

Upvotes: 3

Related Questions