CODEWITHSUNDEEP
sql
Verhogen
Verhogen

Reputation: 28631

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.

Upvotes: 69

Views: 335102

Answers (12)

MomoDev
MomoDev

Reputation: 7

Yes, you just need to add a parenthesis :

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

Upvotes: -1

Abdur Rahman
Abdur Rahman

Reputation: 1656

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;

Upvotes: -1

Angileo
Angileo

Reputation: 9

make it so easy.

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

Upvotes: -4

Tarunjit Singh
Tarunjit Singh

Reputation: 61

@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

Upvotes: 5

ali
ali

Reputation: 21

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

Upvotes: 2

Evakm25
Evakm25

Reputation: 1

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

Upvotes: 0

Mr.Buntha Khin
Mr.Buntha Khin

Reputation: 85

  • 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

Upvotes: 0

jason saldo
jason saldo

Reputation: 9960

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

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332661

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.

Upvotes: 76

eKek0
eKek0

Reputation: 23289

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

Upvotes: 64

PostMan
PostMan

Reputation: 6967

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

Upvotes: 0

Asaph
Asaph

Reputation: 162831

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

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

Upvotes: -1

Related Questions