sql query to get the column value again in case statement

Question

select *, 
case when number = '12' and status = 'y' then cost
end as [price]
from tblx

i got the results from the above query, i want to use the value of price column, again in case statement of the same query as like below

select *, 
case when number = '12' and status = 'y' then cost-500
end as [price],
case when price = 24 then trasdate end as [trasdate]
from tblx

If my question is not clear, please suggest me

I am working in a stored procedure with more than two joins connecting 8 tables, i just want to get the column value again to use it on case statement on the same query, I shortened the question as the query is bigger.

Thanks

Sample data and desired results would help explain what you are trying to do. — Gordon Linoff, Oct 15 '15 at 12:31
if you can't, then why don't you put the whole first clause in the second case? — ergonaut, Oct 15 '15 at 12:33
I want to get the price column value to validate in case when then of the same query. — Kumar, Oct 15 '15 at 12:34
You can't reference an alias that was created at the same scope. Consider using a CTE or subquery, e.g. `SELECT *, CASE WHEN price = 24 THEN trasdate END as trasdate FROM (SELECT *, case when number = '12' ... AS price FROM tblx) AS x;` — Aaron Bertrand, Oct 15 '15 at 12:36
@Aaron Bertrand, Thank You. The query worked in the table, but i am unable to execute from the stored procedure — Kumar, Oct 16 '15 at 04:20

score 1 · Accepted Answer · answered Oct 15 '15 at 12:41

In the same query you need to just duplicate the same case statement where you've attempted to reference it. Btw you should probably add an else 0 to it because currently it'll return null if your conditions don't evaluate to true which could affect its usage in the other case statement.

There are other options if you don't want to duplicate the case statement e.g.:

use the price statement in a sub query and reference it by name in an outer query
use a temp table with price added as a calculated column
as above but use a table variable

Just repeating the statement is obviously simplest and may well perform best too, downside is duplication. In terms of performance, test for your likely use case.

Your answer enables me to have a idea to execute, apart from case expression, Thanks for your efforts — Kumar, Oct 16 '15 at 04:29
No problem, feel free to tick my answer if you're happy with it! ;) — Matt, Oct 16 '15 at 04:33

score 0 · Answer 2 · answered Oct 15 '15 at 12:34

0

select *, 
   case when number = '12' and status = 'y' then cost end price,
   case when number = '12' and status = 'y' 
         and cost = 24 then trasdate end [trasdate]
from tblx

answered Oct 15 '15 at 12:34

Charles Bretana

143,358
22
150
216

This works, but is redundant, and gets worse when the expression to repeat gets more complex. – Aaron Bertrand Oct 15 '15 at 12:37

sql query to get the column value again in case statement

2 Answers2