CASE - QRY - SQL Server Forums

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

SQL Server Development (2000)

CASE - QRY

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-09-09 : 04:47:18

Bottom given qry to be converted.i hav selected qry in CASE is

CASE WHEN RSRV.EFF_TS=@DT
THEN RSRV.GRS_RSRV_AMT
ELSE 0
END AS "Reserve Amount"

this same case is used in same select statement. can i use this case one time and refer for others without function.Need to avoid writing multiple times.another 2 places i have used this qry.

SELECT
CASE WHEN RSRV.EFF_TS=@DT
THEN RSRV.GRS_RSRV_AMT
ELSE 0
END AS "Reserve Amount",
RSRV.ACTUALAMOUNT,

( CASE WHEN RSRV.EFF_TS=@DT
THEN RSRV.GRS_RSRV_AMT
ELSE 0
/
RSRV.ACTUALAMOUNT )

/

(CASE WHEN RSRV.EFF_TS=@DT
THEN RSRV.GRS_RSRV_AMT
ELSE 0 )

FROM RSRV .

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-09 : 04:58:06

Like this

SELECT
	Reserve_Amount,
	RSRV.ACTUALAMOUNT,
	Reserve_Amount/	RSRV.ACTUALAMOUNT,
FROM
(
SELECT *,
	CASE WHEN RSRV.EFF_TS=@DT
	THEN RSRV.GRS_RSRV_AMT
	ELSE 0
	END AS Reserve_Amount
FROM RSRV 
) AS T

Madhivanan

Failing to plan is Planning to fail

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-09-09 : 05:02:59

HOW WILL BE THE PERFORMANCE WHEN COMPARING THIS BOTH? CONSIDERING THIS KIND OF CALCULATION IN TABLE WILL IMPROVE ???

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-09 : 05:05:34

quote:
Originally posted by niranjankumark

HOW WILL BE THE PERFORMANCE WHEN COMPARING THIS BOTH? CONSIDERING THIS KIND OF CALCULATION IN TABLE WILL IMPROVE ???

I think the performance would be same
But if you use derived table approach as I used, you have minimal code which is easy to understand and debug

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources