SQL: Alias Column Name for Use in CASE Statement

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.

Solution 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.

Solution 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

Solution 3:

@OMG Ponies - One of my reasons of not using the following code

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

can be that the t.col1 is not an actual column in the table. For example, it can be a value from a XML column like

Select XMLColumnName.value('(XMLPathOfTag)[1]', 'varchar(max)') 
as XMLTagAlias from Table