Geek asks and answers

CASE T-SQL statement relies on another CASE statement in the same SELECT statement

I have a SELECT query where the result of the second CASE statement may depend on the result of the first CASE statement - something like:

SELECT      ..., 
            CASE 
                WHEN dbo.Table1.Description LIKE '%car%' THEN 'Car'
                WHEN ... 
                ELSE 'Unclassified'
            END AS Product, 
            CASE 
                WHEN dbo.Table2.Description LIKE '%my%brand%' THEN 'Branded'
                WHEN Product='Unclassified' THEN 'Unclassified'
                ELSE 'Generic'
            END AS Brand,
            ...
FROM        ...

If the brand is “unclassified”, if the request cannot find the brand name in the description column, as well as in the column “Product”, the value “Unclassified” is indicated. At the moment, this expression only ever displays the branded brands "Branded" or "Generic". Even when Product is “Unclassified,” it still gives “Generic,” which is not the result I need.

Any ideas?

+1
source share
1 answer

SELECT ( ) - , . , CTE , SELECT:

SELECT
    ...,
    CASE 
        WHEN t.T2Description LIKE '%my%brand%' THEN 'Branded'
        WHEN Product='Unclassified' THEN 'Unclassified'
        ELSE 'Generic'
    END AS Brand
FROM (
    SELECT      ..., 
        CASE 
            WHEN dbo.Table1.Description LIKE '%car%' THEN 'Car'
            WHEN ... 
            ELSE 'Unclassified'
        END AS Product, 
        dbo.Table2.Description as T2Description,
        ...
    FROM        ...
) t
+3

More articles:


All Articles