Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql Alias Column Name for Use in CASE Statement

I have an sql query as below,

select Site,DataSource,
(SELECT CASE 
   WHEN DataSource = 'RFQ' THEN 'ChangeOperator' 
   ELSE 'SameOperator' 
  END) AS OperatorScenario,

  (SELECT CASE 
   WHEN OperatorScenario = 'ChangeOperator' THEN '1' 
   ELSE '022' 
  END) AS OperatorScenario2
from tablename

when i execute this query i am getting exception as Invalid column name OperatorScenario. So i tried to use single codes in alias name in second query as below,

(SELECT CASE 
   WHEN 'OperatorScenario' = 'ChangeOperator' THEN '1' 
   ELSE '022' 
  END) AS OperatorScenario2

So then it executes always else part. Please give me some suggesssions. Regards sangeetha

like image 837
Sangeetha Avatar asked Mar 21 '13 10:03

Sangeetha


People also ask

Can we use alias name in CASE statement?

You can give an alias to a CASE expression exactly the same way as you give an alias to any other expression. After the complete expression, put the optional keyword AS (if you want to) and then the alias.

How do you indicate that a column alias should be used?

Column alias is added in the SELECT statement immediately after the column name. Optionally, you can add the keyword AS in between the column name and the column alias to clearly indicate the use of alias.

Can we use the SELECT statement column name alias in HAVING clause?

When operating in NPS compatibility mode, you can specify the exposed name of a SELECT clause column in the HAVING clause of a query.

Can column alias name be used for WHERE clause in SQL?

In PROC SQL, a column alias can be used in a WHERE clause, ON clause, GROUP BY clause, HAVING clause, or ORDER BY clause. In the ANSI SQL standard and ISO SQL standard, the value that is associated with a column alias does not need to be available until the ORDER BY clause is executed.


2 Answers

The main issue is you cannot reference an alias in the same query. Also you have parentheses and an extra SELECT keyword, making what looks like a subquery where I don't believe you intended one (you do not have a FROM clause).

You can either copy the whole thing again (fixing your query in the process):

SELECT Site, DataSource,
    CASE 
       WHEN DataSource = 'RFQ' THEN 'ChangeOperator' 
       ELSE 'SameOperator' 
    END AS OperatorScenario,

   CASE 
       WHEN 
           CASE 
               WHEN DataSource = 'RFQ' THEN 'ChangeOperator' 
               ELSE 'SameOperator' 
           END = 'ChangeOperator' THEN '1' 
       ELSE '022' 
  END AS OperatorScenario2
from tablename

Or, simplifying:

SELECT Site, DataSource,
    CASE 
       WHEN DataSource = 'RFQ' THEN 'ChangeOperator' 
       ELSE 'SameOperator' 
    END AS OperatorScenario,

   CASE 
       WHEN DataSource = 'RFQ' THEN '1' 
       ELSE '022' 
   END AS OperatorScenario2
from tablename

Or use a CTE (again fixing your query):

WITH cte AS
(
    SELECT Site, DataSource,
    CASE 
       WHEN DataSource = 'RFQ' THEN 'ChangeOperator' 
       ELSE 'SameOperator' 
    END AS OperatorScenario,
    FROM tablename
)
SELECT Site, DataSource, OperatorScenario,
    CASE 
        WHEN OperatorScenario = 'ChangeOperator' THEN '1' 
        ELSE '022' 
    END AS OperatorScenario2
FROM cte
like image 89
lc. Avatar answered Oct 25 '22 18:10

lc.


You cannot access the alias on the same level in this query, either use a sub-query or a cte:

WITH cte 
     AS (SELECT site, 
                datasource, 
                CASE 
                  WHEN datasource = 'RFQ' THEN 'ChangeOperator' 
                  ELSE 'SameOperator' 
                END AS OperatorScenario 
         FROM   dbo.tablename) 
SELECT site, 
       datasource, 
       operatorscenario, 
       CASE 
         WHEN operatorscenario = 'ChangeOperator' THEN '1' 
         ELSE '022' 
       END AS OperatorScenario2 
FROM   cte 

Note that i've also removed the extra parantheses and select in the CASE.

like image 31
Tim Schmelter Avatar answered Oct 25 '22 19:10

Tim Schmelter