SQL: Alias Column Name for Use in CASE Statement

Question

Is it possible to alias a column name and then use that in a CASE statement? For example,

SELECT col1 as a, CASE WHEN a = 'test' THEN 'yes' END as value FROM table; 

I am trying to alias the column because actually my CASE statement would be generated programmatically, and I want the column that the case statement uses to be specified in the SQL instead of having to pass another parameter to the program.

Answer 1

This:

SELECT col1 as a,        CASE WHEN a = 'test' THEN 'yes' END as value    FROM table; 

...will not work. This will:

SELECT CASE WHEN a = 'test' THEN 'yes' END as value   FROM (SELECT col1 AS a           FROM TABLE) 

Why you wouldn't use:

SELECT t.col1 as a,        CASE WHEN t.col1 = 'test' THEN 'yes' END as value    FROM TABLE t; 

...I don't know.

Answer 2

I think that MySql and MsSql won't allow this because they will try to find all columns in the CASE clause as columns of the tables in the WHERE clause.

I don't know what DBMS you are talking about, but I guess you could do something like this in any DBMS:

SELECT *, CASE WHEN a = 'test' THEN 'yes' END as value FROM (    SELECT col1 as a FROM table ) q