Calculations with Derived Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculations with Derived Columns

Author

Topic

Sschuster
Starting Member

26 Posts

Posted - 2005-09-12 : 10:19:49

I am wondering if it is possible to perform a calulation on a derived column within the same query. For instance, I have a stored proc that accepts a parameter (param1) and based on that value looks up a value in another table (I use a CASE statement with additional Select queries to get the additional value) and then alias that result so that I can display it. creating a derived column. The problem arises when, later in the query I want to multiply a value by the result of the subquery that was executed in the case statement. When I try this it gives me an invalid column name, listing the derived column's name as the invalid column name.

Is it possible to use a derived column in a calculation within the same query?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-12 : 10:24:40

Post some sample data, expected result and the query you used

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-12 : 10:26:18

no you can't directly.
you can however do this:
select * from
(
select ..., case ... end as yourCalcualtedColumn
from ...
) t
where yourCalcualtedColumn > 100

Go with the flow & have fun! Else fight the flow

Sschuster
Starting Member

26 Posts

Posted - 2005-09-12 : 10:34:28

Here is the case statement that is creating the derived column...
Convert( Decimal(10,4),
Case
When O.CurrencyItem Like '$%' Then
(Select ClosePrice From PFX_Currency Where Item = O.CurrencyItem)
When len( O.CurrencyItem) = 6 Then
(Select Case
When O.CurrencyItem = 'EURCHF' Then
(Select ClosePrice From PFX_Currency Where Pair = 'USD/CHF')
When O.CurrencyItem = 'EURGBP' Then
(Select ClosePrice From PFX_Currency Where Pair = 'GBP/USD')
When O.CurrencyItem = 'EURJPY' Then
(Select ClosePrice From PFX_Currency Where Pair = 'USD/JPY')
When O.CurrencyItem = 'GBPCHF' Then
(Select ClosePrice From PFX_Currency Where Pair = 'USD/CHF')
End)
Else
1
End) As CCUSD

So this should create a column for me called CCUSD. Then later in the query I need to take this value and muliply it by another value, essentially converting a foreign currency to US dollars. Is this possible?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-12 : 10:36:50

Use the logic spirit1 suggested

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-12 : 10:37:07

yes when used in the form i gave you.
basiclly wrap your query in another select and you'll be able to use the derived column with the name you provide.

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-12 : 10:56:52

quote:
Originally posted by Sschuster

Here is the case statement that is creating the derived column...
Convert( Decimal(10,4),
Case
When O.CurrencyItem Like '$%' Then
(Select ClosePrice From PFX_Currency Where Item = O.CurrencyItem)
When len( O.CurrencyItem) = 6 Then
(Select Case
When O.CurrencyItem = 'EURCHF' Then
(Select ClosePrice From PFX_Currency Where Pair = 'USD/CHF')
When O.CurrencyItem = 'EURGBP' Then
(Select ClosePrice From PFX_Currency Where Pair = 'GBP/USD')
When O.CurrencyItem = 'EURJPY' Then
(Select ClosePrice From PFX_Currency Where Pair = 'USD/JPY')
When O.CurrencyItem = 'GBPCHF' Then
(Select ClosePrice From PFX_Currency Where Pair = 'USD/CHF')
End)
Else
1
End) As CCUSD

So this should create a column for me called CCUSD. Then later in the query I need to take this value and muliply it by another value, essentially converting a foreign currency to US dollars. Is this possible?

It looks like you are using CASE statements instead of joins in your SELECT. If you have a simple table that connects each CurrencyItem value to each Pair value, you can probably just use joins and not have to write such complicated SQL. In addition, your SELECT has lots of *data* stored in it, as opposed to just logic, meaning that as your data changes (i.e., more currencies are used) you will need to alter your code as opposed to alterting your data.

Sschuster
Starting Member

26 Posts

Posted - 2005-09-12 : 11:13:26

JSmith - I agree with you completely. I am actually going back through a lot of our stored procs and rewriting them. What I'm trying to do now is a fix to an existing proc and at this don't have time to rewrite. But I agree with your comments 100%, thanks.

Spirit - Here is the entire query (It's a mess, I know). How does your suggestion fit in to this? You can see my derived column near the top (CCUSD) and then where I am trying to use it is right below it. Any feedback is appreciated.

SELECT Distinct
O.Ticket,
O.AccountNumber,
O.RollType,
Convert( Decimal(10,4),
Case
When O.CurrencyItem Like '$%' Then
(Select ClosePrice From PFX_Currency Where Item = O.CurrencyItem)
When len( O.CurrencyItem) = 6 Then
(Select Case
When O.CurrencyItem = 'EURCHF' Then
(Select ClosePrice From PFX_Currency Where Pair = 'USD/CHF')
When O.CurrencyItem = 'EURGBP' Then
(Select ClosePrice From PFX_Currency Where Pair = 'GBP/USD')
When O.CurrencyItem = 'EURJPY' Then
(Select ClosePrice From PFX_Currency Where Pair = 'USD/JPY')
When O.CurrencyItem = 'GBPCHF' Then
(Select ClosePrice From PFX_Currency Where Pair = 'USD/CHF') End)
Else
1
End) As CCUSD,
Convert(char(15),(Case
When O.BuySell = '1' Then
((C.Bid - O.FillPrice) * Abs(O.OffsetQty)) / CCUSD
Else
((C.Ask - O.FillPrice) * Abs(O.OffsetQty)) / CCUSD
End )) As ProfitLoss,
Convert(Real,0.0) As USDollars,
Convert(Real,0.0) As Margin,
O.BuySell,
O.CurrencyItem,
O.Currencypair,
Case
When O.BuySell = '1' Then
(Select 'Long')
Else
(Select 'Short')
End as OrderMode, -- case
O.Quantity,
O.OffsetQty,
O.FillPrice,
Convert(char(10), O.FillDate, 101) as FillDate,
Convert(char(8), O.FillTime, 108) as FillTime,
O.OrderType,
T.[Description],
O.LoginId,
O.ManagedByAccount,
S.[Description] as OrderStatus,
O.BestOrderNumber,
O.Price,
O.Limit,
O.OrderLink,
O.Status,
Case
When O.GTC = '1' Then
(Select 'GTC')
Else
(Select 'Day')
End as Session,
Case
When O.BlockOrder = '1' Then
(Select 'Yes')
Else
(Select 'No')
End as BlockOrder, -- case
Case
When O.BlockOrder = '1' Then
(Select FirstName + ' ' + LastName From PFX_UserInfo Where O.ManagedByAccount = AccountNumber)
Else
(Select FirstName + ' ' + LastName From PFX_UserInfo Where O.AccountNumber = AccountNumber)
End as SubmittedBy, -- case
O.ValueDate,
Convert(char(10), O.OrderDate, 101) as OrderDate,
Convert(char(8), O.OrderTime, 108) as OrderTime,
O.DealerOrder,
O.OrderFrom,
O.TransId,
(O.AccountNumber + O.CurrencyPair) as [Key],
O.MiniForex

From
PFX_Orders O,
PFX_OrderTypes T,
PFX_OrderStatus S,
PFX_Currency C

Where
((O.AccountNumber = @Account) OR (O.ManagedByAccount = @Account))
AND (O.Status = 6)
AND O.OrderType = T.OrderType
AND O.Status = S.OrderStatus
AND O.CurrencyItem like @CurrencyItem
And O.OffSetQty <> 0
And C.Item = O.CurrencyItem

Order by O.FillDate DESC, O.FillTime DESC, O.Ticket DESC
End

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-12 : 11:19:19

[code]
select *,
Convert(char(15),(Case
When BuySell = '1' Then
((Bid - FillPrice) * Abs(OffsetQty)) / CCUSD
Else
((Ask - FillPrice) * Abs(OffsetQty)) / CCUSD
End)) As ProfitLoss
from
(
SELECT Distinct
O.Ticket,
O.AccountNumber,
O.RollType,
Convert( Decimal(10,4),
Case
When O.CurrencyItem Like '$%' Then
(Select ClosePrice From PFX_Currency Where Item = O.CurrencyItem)
When len( O.CurrencyItem) = 6 Then
(Select Case
When O.CurrencyItem = 'EURCHF' Then
(Select ClosePrice From PFX_Currency Where Pair = 'USD/CHF')
When O.CurrencyItem = 'EURGBP' Then
(Select ClosePrice From PFX_Currency Where Pair = 'GBP/USD')
When O.CurrencyItem = 'EURJPY' Then
(Select ClosePrice From PFX_Currency Where Pair = 'USD/JPY')
When O.CurrencyItem = 'GBPCHF' Then
(Select ClosePrice From PFX_Currency Where Pair = 'USD/CHF') End)
Else
1
End) As CCUSD,
Convert(Real,0.0) As USDollars,
Convert(Real,0.0) As Margin,
O.BuySell,
O.CurrencyItem,
O.Currencypair,
Case
When O.BuySell = '1' Then
(Select 'Long')
Else
(Select 'Short')
End as OrderMode, -- case
O.Quantity,
O.OffsetQty,
O.FillPrice,
Convert(char(10), O.FillDate, 101) as FillDate,
Convert(char(8), O.FillTime, 108) as FillTime,
O.OrderType,
T.[Description],
O.LoginId,
O.ManagedByAccount,
S.[Description] as OrderStatus,
O.BestOrderNumber,
O.Price,
O.Limit,
O.OrderLink,
O.Status,
Case
When O.GTC = '1' Then
(Select 'GTC')
Else
(Select 'Day')
End as Session,
Case
When O.BlockOrder = '1' Then
(Select 'Yes')
Else
(Select 'No')
End as BlockOrder, -- case
Case
When O.BlockOrder = '1' Then
(Select FirstName + ' ' + LastName From PFX_UserInfo Where O.ManagedByAccount = AccountNumber)
Else
(Select FirstName + ' ' + LastName From PFX_UserInfo Where O.AccountNumber = AccountNumber)
End as SubmittedBy, -- case
O.ValueDate,
Convert(char(10), O.OrderDate, 101) as OrderDate,
Convert(char(8), O.OrderTime, 108) as OrderTime,
O.DealerOrder,
O.OrderFrom,
O.TransId,
(O.AccountNumber + O.CurrencyPair) as [Key],
O.MiniForex

From
PFX_Orders O,
PFX_OrderTypes T,
PFX_OrderStatus S,
PFX_Currency C

Where
((O.AccountNumber = @Account) OR (O.ManagedByAccount = @Account))
AND (O.Status = 6)
AND O.OrderType = T.OrderType
AND O.Status = S.OrderStatus
AND O.CurrencyItem like @CurrencyItem
And O.OffSetQty <> 0
And C.Item = O.CurrencyItem
) t
Order by FillDate DESC, FillTime DESC, Ticket DESC
[/code]

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources