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

User · Accepted Answer

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

User · Answer

Not in MySQL  I tried it and I get the following error   ERROR 1054  42S22   Unknown column  a  in  field list

User · Answer

In MySql  alice name may not work  therefore put the original column name in the CASE statement   SELECT col1 as a  CASE WHEN col1    test  THEN  yes  END as value FROM table    Sometimes above query also may return error  I don t know why  I faced this problem in my two different development machine   Therefore put the CASE statement into the         as below   SELECT col1 as a   CASE WHEN col1    test  THEN  yes  END  as value FROM table

User · Answer

It should work  Try this  Select   from                select col1  col2  case when 1 1 then  ok  end as alias col                from table          as tmp table order by         case when  sortBy    1 then tmp table alias col end asc

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

make it so easy   select columnnameshow    CASE tipoventa when  CONTADO  then  contadito  when  CREDITO  then  cred  else  no result  end  from Promocion Promocion

User · Answer

Nor in MsSql  SELECT col1 AS o  e   CASE WHEN o  lt  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

User · Answer

Yes  you just need to add a parenthesis   SELECT col1 as a   CASE WHEN a    test  THEN  yes  END  as value FROM table

User · Answer

If you write only equal condition just  Select Case columns1 When 0 then  Value1   when 1 then  Value2  else  Unknown  End If you want to write greater   Less then or equal you must do like this  Select Case When  ColumnsName   0 then  value1  When  ColumnsName  0 Or   ColumnsName  lt 0 then  value2   Else  Unkownvalue  End   From tablename  Thanks Mr Buntha Khin

User · Answer

SELECT     a AS  blabla a       b  blabla b       CASE c         WHEN 1 THEN  aaa          WHEN 2 THEN  bbb          ELSE  unknown       END AS  my alias        d AS  blabla d  FROM mytable

[sql] SQL: Alias Column Name for Use in CASE Statement

Examples related to sql