SQL: Alias Column Name for Use in CASE Statement

Sql

Sql Problem Overview


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.

Sql Solutions


Solution 1 - Sql

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 - Sql

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 - Sql

@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

Solution 4 - Sql

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

Solution 5 - Sql

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

Solution 6 - Sql

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

Solution 7 - Sql

  • 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]<0 then 'value2' Else 'Unkownvalue' End

From tablename

Thanks Mr.Buntha Khin

Solution 8 - Sql

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

Solution 9 - Sql

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

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

Solution 10 - Sql

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;

Solution 11 - Sql

Yes, you just need to add a parenthesis :

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

Solution 12 - Sql

make it so easy.

select columnnameshow = (CASE tipoventa
when 'CONTADO' then 'contadito'
when 'CREDITO' then 'cred'
else 'no result'
end) from Promocion.Promocion 

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionVerhogenView Question on Stackoverflow
Solution 1 - SqlOMG PoniesView Answer on Stackoverflow
Solution 2 - SqleKek0View Answer on Stackoverflow
Solution 3 - SqlTarunjit SinghView Answer on Stackoverflow
Solution 4 - SqlaliView Answer on Stackoverflow
Solution 5 - Sqljason saldoView Answer on Stackoverflow
Solution 6 - SqlPostManView Answer on Stackoverflow
Solution 7 - SqlMr.Buntha KhinView Answer on Stackoverflow
Solution 8 - SqlEvakm25View Answer on Stackoverflow
Solution 9 - SqlAsaphView Answer on Stackoverflow
Solution 10 - SqlAbdur RahmanView Answer on Stackoverflow
Solution 11 - SqlMomoDevView Answer on Stackoverflow
Solution 12 - SqlAngileoView Answer on Stackoverflow