Geek asks and answers

Sql alias column name to use in CASE statement

I have a sql query as shown 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 get the exception as an invalid column name OperatorScenario. Therefore, I tried to use single codes in the alias name in the second query, as shown below,

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

So he always does the other part. Please give me some advice. Regards Sangeetha

+5
source share
3 answers

The main problem is that you cannot reference an alias in the same query. In addition, you have parentheses and an additional keyword SELECT, which makes it look like a subquery in which I do not consider what you intend (you have no suggestion FROM).

( ):

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

, :

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

CTE ( ):

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
+6

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

, CASE.

+3

I set my aliases at the beginning as follows:

SELECT [ALIAS] = CASE....

Your will be:

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

   [OperatorScenario2] = CASE 
       WHEN DataSource = 'RFQ' THEN '1' ELSE '022' 
   END
from tablename
0
source

More articles:


All Articles