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

Not in MySQL. I tried it and I get the following error:

ERROR 1054 (42S22): Unknown column 'a' in 'field list'

Answer 2

Nor in MsSql

SELECT col1 AS o, e = CASE WHEN o < GETDATE() THEN o ELSE GETDATE() END 
FROM Table1

Returns:

Msg 207, Level 16, State 3, Line 1
Invalid column name 'o'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'o'.

However if I change to CASE WHEN col1... THEN col1 it works

Answer 3

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

Answer 4

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 5

I use CTEs to help compose complicated SQL queries but not all RDBMS' support them. You can think of them as query scope views. Here is an example in t-sql on SQL server.

With localView1 as (
 select c1,
        c2,
        c3,
        c4,
        ((c2-c4)*(3))+c1 as "complex"
   from realTable1) 
   , localView2 as (
 select case complex WHEN 0 THEN 'Empty' ELSE 'Not Empty' end as formula1,
        complex * complex as formula2    
   from localView1)
select *
from localView2