Use calculated column name in query again in the same query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Use calculated column name in query again in...

Use calculated column name in query again in the same query

Rate Topic

Display Mode

Topic Options

Author

Message

manojkanoi

Posted Wednesday, November 18, 2009 3:05 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 04, 2010 12:19 AM
Points: 94, Visits: 60

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?

Post #821231

Phil Parkin

Posted Wednesday, November 18, 2009 3:19 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 4,324, Visits: 9,665

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

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #821240

manojkanoi

Posted Wednesday, November 18, 2009 3:25 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 04, 2010 12:19 AM
Points: 94, Visits: 60

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?

Post #821247

Matt Miller (#4)

Posted Wednesday, November 18, 2009 3:41 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:45 PM
Points: 7,002, Visits: 13,999

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?

Post #821257

Jus

Posted Thursday, November 19, 2009 8:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 29, 2010 2:48 AM
Points: 124, Visits: 275

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

Post #821651

LutzM

Posted Thursday, November 19, 2009 12:10 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:04 PM
Points: 6,739, Visits: 12,167

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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #821862

« Prev Topic | Next Topic »

Permissions