November 18, 2009 at 3:05 pm
I want to Use calculated column name in query again in the same query.
For eg:
select productname, (uprice+sprice) As pprice, CASE WHEN IsNull(pprice) Then '0' Else pprice End As pprice2 from products
Here pprice is the value I am calculating and want to be able to refer it in the same query without having to write the calculation again. This is just a simple example but the actual calculated column is complicated and very long and it makes it very difficult to debug if I have to use the same code again and again. Is there a cleaner way to do this in same query?
November 18, 2009 at 3:19 pm
This sounds like a pure SQL question, not SSIS? Anyhow, there is a way, using Common Table Expressions (or CTEs).
Using your example, it would be something like:
With Data as
(select productname, (uprice+sprice) As pprice
from xxxxx)
select data.productname, CASE WHEN IsNull(data.pprice) Then '0' Else data.pprice End As pprice2
from data
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 18, 2009 at 3:25 pm
Thanks but this seems like it will execute the first query to get the column values and then execute the main query again which will take much longer than executing it as a single query. There is no way to do it without using a new table?
November 18, 2009 at 3:41 pm
Phil is showing you what is called a Common Table Expression. Unless you put that calculated column in your table, that's the way to do what you are asking.
You should try it out - how it's written often has very little to do with exactly how it executes. Don't assume it will necessarily do a two-pass query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 19, 2009 at 8:23 am
For getting faster response, please post readily consumable sample script.
for now i have created a table with dummy data and calling the calculated price column at multiple places in the select statement by actually calculating only once.
create table MultiUSe (Pid int,productname varchar(10),Uprice int,SPrice int)
insert into MultiUSe values (1,'aaa',10,20)
insert into MultiUSe values(2,'bbb',30,40)
insert into MultiUSe values(3,'ccc',50,60)
insert into MultiUSe values(4,'ddd',70,80)
insert into MultiUSe values(5,'eee',90,100)
insert into MultiUSe (Pid,Productname) values(6,'fff')
With DataCTE as
(select productname, (uprice+sprice) As pprice
from MultiUSe)
select productname, pprice 'pp1',pprice 'pp2',pprice 'pp3' from DataCTE
November 19, 2009 at 12:10 pm
Or you could replace [pprice] with its original calculated fields (please note that I change your query in terms of ISNULL usage):
select productname, (uprice+sprice) As pprice, IsNull(uprice+sprice,0) As pprice2
from products
Edit: Another option would be to add a computed column to the source table containing your calculation.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply