Query Help

Question

Post reply

Query Help

Virender Sihag

Say Hey Kid

Points: 688
More actions
June 25, 2010 at 1:10 am

#223192

can i set a alias or assign to a variable
of a column so that i can use the same in the other column where i dont have to write all the logic again.
For the clarity i need like this.
select NetQty=(SellQty-BuyQty),
NetPrice=(SellQty-BuyQty) * Rate
from TransactionTable
select NetQty=(sellQty-BuyQty),
NetPrice=(sellQty-BuyQty) * Rate
-- i need like this NetPrice=NetQty*Rate
From TransactionTable
although my formula is more complex than this 🙂
[font="Arial Narrow"]Failure is not the worst thing. The worst Thing is not to try[/font]

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

I don't quite get the question.

Can you post an example? Basically I would like to see:

* A CREATE TABLE script

* Some sample data, scripted as INSERT into your test table

* The desired output, based on the sample data

If in doubt, take a look at the article linked in my signature line.

-- Gianluca Sartori

Kingston Dhasian SSCoach Points: 19795 More actions · Answer 2

You can make use of a Derived Table like below

SELECTNetQty, NetPrice = NetQty * Rate

FROM(

SELECT NetQty = ( sellQty - BuyQty )

FROM TransactionTable

) T

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 3

Probably be easiest to create a view if you need to use the calculation in multiple statements

Clear Sky SQL
My Blog[/url]

Virender Sihag Say Hey Kid Points: 688 More actions · Answer 4

hi Gianluca,

here is an example

create table #TransactionTable

(

ClientCode varchar(50),

buyqty bigint,

sellqty bigint,

rate decimal

)

insert into #TransactionTable values('1001',20,30,10)

insert into #TransactionTable values('1001',10,30,8)

insert into #TransactionTable values('1001',80,30,9)

insert into #TransactionTable values('1002',2,30,20)

insert into #TransactionTable values('1002',1,30,21)

insert into #TransactionTable values('1002',8,30,22)

select NetQty=(SellQty-BuyQty),

NetPrice=(SellQty-BuyQty) * Rate

from #TransactionTable

can i use this query some way like this

select NetQty=(sellQty-BuyQty),

NetPrice=Netqty * Rate

-- i need like this NetPrice=NetQty*Rate

From #TransactionTable

[font="Arial Narrow"]Failure is not the worst thing. The worst Thing is not to try[/font]

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 5

Then you can use a derived table as Kingston suggested, or a CTE

Clear Sky SQL
My Blog[/url]

Virender Sihag Say Hey Kid Points: 688 More actions · Answer 6

hi kingstom,dev

thank for u ur quick reply.

derived/nested query is ok. but we will have a performance issue with this.

i thought there would be some other way around if i want to do it in the same query.

may be i take help of a local varaible

becuase my calculations are on the same session

[font="Arial Narrow"]Failure is not the worst thing. The worst Thing is not to try[/font]

spaghettidba SSC Guru Points: 105712 More actions · Answer 7

Dave Ballantyne (6/25/2010)
Then you can use a derived table as Kingston suggested, or a CTE

Agreed. Thanks for posting DDL and sample data.

What Dave is calling a CTE is something like this:

;WITH IntermediateQuery

AS ( SELECT NetQty = ( sellQty - BuyQty ), *

FROM #TransactionTable

)

SELECT NetQty,

NetPrice = NetQty * Rate

FROM IntermediateQuery

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105712 More actions · Answer 8

virender.singh (6/25/2010)
derived/nested query is ok. but we will have a performance issue with this.

What kind of performance problem? The way of accessing the table suggested by Kingston isn't any different from accessing the table directly.

-- Gianluca Sartori

Paul White SSC Guru Points: 150457 More actions · Answer 9

Other options:

CREATE TABLE #TransactionTable

(

clientcode VARCHAR(50) NOT NULL,

buyqty BIGINT,

sellqty BIGINT,

rate DECIMAL(5,2)

);

INSERT #TransactionTable VALUES('1001', 20, 30, 10);

INSERT #TransactionTable VALUES('1001', 10, 30, 8);

INSERT #TransactionTable VALUES('1001', 80, 30, 9);

INSERT #TransactionTable VALUES('1002', 2, 30, 20);

INSERT #TransactionTable VALUES('1002', 1, 30, 21);

INSERT #TransactionTable VALUES('1002', 8, 30, 22);

SELECT iTVF.NetQty,

NetPrice = iTVF.NetQty * Rate

FROM #TransactionTable T

CROSS

APPLY (SELECT SellQty - BuyQty) iTVF (NetQty);

-- OR

-- Computed column (persisted and/or indexed as required)

ALTER TABLE #TransactionTable

ADD

NetQty AS SellQty - BuyQty

PERSISTED NOT NULL;

SELECT T.NetQty,

NetPrice = NetQty * Rate

FROM #TransactionTable T;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Kingston Dhasian SSCoach Points: 19795 More actions · Answer 10

Gianluca Sartori (6/25/2010)
virender.singh (6/25/2010)
derived/nested query is ok. but we will have a performance issue with this.
What kind of performance problem? The way of accessing the table suggested by Kingston isn't any different from accessing the table directly.

I agree with Gianluca. I don't think there will be any performance impact as the table will be accessed only once and not twice as you might be thinking.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Virender Sihag Say Hey Kid Points: 688 More actions · Answer 11

thank you paul

🙂

[font="Arial Narrow"]Failure is not the worst thing. The worst Thing is not to try[/font]