Thursday, December 10, 2009

Sample for CASE in Oracle SQL

select a.MOBILENUMBER,a.PLANCODE,
a.SRC_CUR_MTH_CHRG, a.TGT_CUR_MTH_CHRG, abs(abs(a.SRC_CUR_MTH_CHRG)-abs(a.TGT_CUR_MTH_CHRG)) DIFF,
CASE WHEN
(abs(nvl(a.SRC_CUR_MTH_CHRG,0))>abs(nvl(a.TGT_CUR_MTH_CHRG,0)))
THEN
(round(nvl((a.SRC_CUR_MTH_CHRG-
.TGT_CUR_MTH_CHRG)/nullif(a.SRC_CUR_MTH_CHRG,0)*100,0),2))
ELSE
(round(nvl((a.SRC_CUR_MTH_CHRG-a.TGT_CUR_MTH_CHRG)/nullif(a.TGT_CUR_MTH_CHRG,0)*100,0),2))
END
VAR_CURMTH_CHRG from reconn.br_master_table a where a.MOBILENUMBER='911120093786'

No comments: