mercredi 24 juin 2015

Using the result of a subquery in a CASE expression with T-SQL

I'm writing a query with some CASE expressions and it outputs helper-data columns which help me determine whether or not a specific action is required. I would like to know if I can somehow use the result of a subquery as the output without having to perform the same query twice (between WHEN (subquery) THEN and as the result after THEN)

The dummy code below describes what I'm after. Can this be done? I'm querying a MS2005 SQL database.

SELECT   'Hello StackOverflow'
        ,'Thanks for reading this question'
        ,CASE
            WHEN 
                (
                    SELECT count(*)
                    FROM    sometable
                    WHERE   condition = 1
                    AND     somethingelse = 'value'
                ) > 0 THEN
                    -- run the query again to get the number of rows
                    (
                        SELECT count(*)
                        FROM    sometable
                        WHERE   condition = 1
                        AND     somethingelse = 'value'
                    )
            ELSE 0
         END

SELECT   'Hello StackOverflow'
        ,'Thanks for reading this question'
        ,CASE
            WHEN 
                (
                    SELECT count(*)
                    FROM    sometable
                    WHERE   condition = 1
                    AND     somethingelse = 'value'
                ) AS subqry_count > 0 THEN
                    -- use the subqry_count, which fails... "Incorrect syntax near the keyword 'AS'"
                    subqry_count
            ELSE 0
         END

Aucun commentaire:

Enregistrer un commentaire