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
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.
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.
When operating in NPS compatibility mode, you can specify the exposed name of a SELECT clause column in the HAVING clause of a query.
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.
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
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With