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

Question

I have a SELECT query where the result of the second CASE statement can 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        ...

Where Brand is 'Unclassified' if the query can't find the brand name in a description column and the Product column has also been determined to have value 'Unclassified'. At the moment this statement only ever outputs 'Branded' or 'Generic' Brand types. Even when Product is 'Unclassified' it still gives 'Generic' which is not the output I need.

Any ideas?

Answer 1

The results of a SELECT clause are computed (as if they're being evaluated) in parallel - as such, one column's value cannot depend on another one's. The solution is to introduce a CTE or subquery so that you have multiple SELECT clauses:

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