February 4, 2008 at 10:38 am
Hi there,
I'm trying to use a derived column in a calculation in the same query...
'Monthly Pac' = CASE
WHEN tPolicyBillPrem.POL_BILL_TYP_CD = '4' AND tPolicyA.POL_CSTAT_CD = '1' THEN tPolicyBillPrem.POL_SNDRY_AMT
ELSE 0
END,
'Annualized Premium' = CASE
WHEN tPolicyBillPrem.POL_BILL_MODE_CD = '01' THEN [Monthly Pac] * 12
ELSE 0
END,
I would like to use Monthly Pac to calculate 'Annualized Premium'. How do I tell SQL Server that I want to use my derived column in the next case statement?
February 4, 2008 at 11:10 am
Unfortunately, you cannot use derived columns to compute on other columns in the same query. You would have to put the MontlyPac case statement in the then section of "Annualized Premium" case. Your only other option would be to use a function to get the "MonthlyPac" value
e.g.
SELECT
'Monthly Pac' = dbo.udf_GetMonthlyPac(tPolicyBillPrem.POL_BILL_TYP_CD,tPolicyA.POL_CSTAT_CD),
'Annualized Premium' =
CASE
WHEN tPolicyBillPrem.POL_BILL_MODE_CD = '01' THEN
dbo.udf_GetMonthlyPac(tPolicyBillPrem.POL_BILL_TYP_CD,tPolicyA.POL_CSTAT_CD) * 12
ELSE 0
END
February 4, 2008 at 2:04 pm
The "right" way is to put the calculated column in an inline view so that you have access to the name in the outer query. Oh and No it does not inccur in worse performance ... just some different syntax:
Ex
select CalculatedColumn1 + Myformula + 1234
from
(
select xyz + sdc as CalculatedColumn1 , .... from table
) inline_view
* Noel
February 4, 2008 at 2:07 pm
I'd suggest you use a nested table expression, meaning
select nestedTSel.*
, [Monthly Pac] ....
from ( select 'Monthly Pac' = CASE
WHEN tPolicyBillPrem.POL_BILL_TYP_CD = '4' AND tPolicyA.POL_CSTAT_CD = '1' THEN tPolicyBillPrem.POL_SNDRY_AMT
ELSE 0
END,
'Annualized Premium' = CASE
WHEN tPolicyBillPrem.POL_BILL_MODE_CD = '01' THEN [Monthly Pac] * 12
ELSE 0
END, ....
from whatever
) nestedTSel
I'd prefer this rather than a udf.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 4, 2008 at 5:43 pm
I'd suggest you use a nested table expression, meaning
select nestedTSel.*, [Monthly Pac] ....from ( select 'Monthly Pac' = CASEWHEN tPolicyBillPrem.POL_BILL_TYP_CD = '4' AND tPolicyA.POL_CSTAT_CD = '1' THEN tPolicyBillPrem.POL_SNDRY_AMTELSE 0END,'Annualized Premium' = CASEWHEN tPolicyBillPrem.POL_BILL_MODE_CD = '01' THEN [Monthly Pac] * 12ELSE 0END, .... from whatever) nestedTSel
I'd prefer this rather than a udf.
I like this method too 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply