昨天在分析贷款逾期存储过程,碰到如下游标定义:
(CASE
WHEN(CASE
WHEN NVL(MM.ProdOrgYN, 'N') = 'Y' THEN 'Y' ELSE 'N'
END) = 'Y'
THEN
CASE WHEN
pack_osi_util.IsBusDay_Org(AR.DueDate + NVL(AL.GraceDays, 0)) = 'Y'
THEN
CASE WHEN
AR.DueDate + NVL(AL.GraceDays, 0) =
To_Date('2019-12-20', 'yyyy-mm-dd') THEN 'Y' ELSE 'N'
END
ELSE CASE WHEN
pack_osi_util.GetBusDateForward_Org(AR.DueDate +
NVL(AL.GraceDays, 0),
1) =
To_Date('2019-12-20', 'yyyy-mm-dd') THEN 'Y' ELSE 'N'
END
END
ELSE
CASE WHEN
AR.DueDate + NVL(AL.GraceDays, 0) =
To_Date('2019-12-20', 'yyyy-mm-dd') THEN 'Y' ELSE 'N' END
END) = 'Y'
分析了大半天,
小结:
case when end也可用于where条件,以前我只用于select中
case when可以多级嵌套,如:
select
case when (另一个case when表达式)='某个值'
case when end --其为子case when语句
再次说明sql的博大精深,要努力啊
为了备记,特附上相关贷款逾期的游标:
CURSOR curs_GetOverdueAccounts IS
SELECT ASA.AcctNbr,
ASA.SubAcctNbr,
ASA.BalTypCd,
AL.GraceDays
FROM Acct A,
AcctSubAcct ASA,
AcctLoan AL,
AcctRcvb AR,
RcvbStat RS,
MjMiAcctBalCatTyp MMBC,
MjMiAcctTyp MM--BBP修改对公日历时增加
WHERE A.CurrAcctStatCd IN ( 'ACT',
'NPFM')
AND A.AcctNbr = AR.AcctNbr
AND AL.AcctNbr = AR.AcctNbr
/*BIBAOPING--修改对公日历时注释掉--Start:2010-09-27
AND ( AR.DueDate + AL.GraceDays >= cpdFromDate AND
AR.DueDate + AL.GraceDays <= cpdThruDate )
BIBAOPING--修改对公日历时注释掉--end:2010-09-27 */
/*THE RCVBSTATE IS PPD(Partially Paid) AND ACT(Active)*/
AND RS.RcvbStatCd = AR.CurrRcvbStatCd
AND RS.PaidYN = 'N'
AND ASA.AcctNbr = AR.AcctNbr
AND ASA.SubAcctNbr = AR.SubAcctNbr
/*THE FILTER OF ACCOUNTS THAT NOT CONFIG THE BALANCE TYPE*/
AND MMBC.MjAcctTypCd = A.MjAcctTypCd
AND MMBC.MiAcctTypCd = A.CurrMiAcctTypCd
AND MMBC.BalCatCd = ASA.BalCatCd
AND MMBC.BalTypCd = ASA.BalTypCd
-- AND A.ACCTNBR=183903044990 --WFZ TEST
--AND MMBC.PaymentSeqNbr > 0
/*BIBAOPING--修改对公日历时增加--Start:2010-09-27*/
AND
MMBC.MjAcctTypCd=MM.MjAcctTypCd--BBP
AND
MMBC.MiAcctTypCd=MM.MiAcctTypCd--BBP
/*BIBAOPING--修改对公日历时增加--End:2010-09-27*/
AND (AR.AcctNbr,
AR.SubAcctNbr) IN(
SELECT ACCTNBR,
SubAcctNbr
FROM AcctSubAcct
WHERE BalCatCd=lcsBalCatCd_NOTE
AND( /*NOTE/BAL:ODP/BAL|ODP/INT*/
(BaltypCd=lcsBalTypCd_BAL
AND AcctNbr IN(
SELECT ODPBALTABLE.AcctNbr
FROM (SELECT AcctNbr FROM AcctSubAcct where balcatcd=lcsBalCatCd_ODP and BaltypCd=lcsBalTypCd_BAL)ODPBALTABLE,
(SELECT AcctNbr FROM AcctSubAcct where balcatcd=lcsBalCatCd_ODP and BaltypCd=lcsBalTypCd_INT)ODPINTTABLE
WHERE ODPBALTABLE.AcctNbr=ODPINTTABLE.AcctNbr))
OR /*NOTE/INT:ODI/BAL|ODI/INT*/
(BaltypCd=lcsBalTypCd_INT
AND AcctNbr IN(
SELECT ODIBALTABLE.AcctNbr
FROM (SELECT AcctNbr FROM AcctSubAcct where balcatcd=lcsBalCatCd_ODI and BaltypCd=lcsBalTypCd_BAL)ODIBALTABLE,
(SELECT AcctNbr FROM AcctSubAcct where balcatcd=lcsBalCatCd_ODI and BaltypCd=lcsBalTypCd_INT)ODIINTTABLE
WHERE ODIBALTABLE.AcctNbr=ODIINTTABLE.AcctNbr))
)
GROUP BY ACCTNBR,
SubAcctNbr)
/*BIBAOPING--修改对公日历时增加--Start:2010-09-27*/
AND (
CASE WHEN (CASE WHEN NVL(MM.ProdOrgYN, 'N') = 'Y' THEN 'Y' ELSE 'N' END) = 'Y' THEN
CASE WHEN pack_osi_util.IsBusDay_Org(AR.DueDate + NVL(AL.GraceDays,0)) = 'Y' THEN
CASE WHEN AR.DueDate + NVL(AL.GraceDays,0) = To_Date(in_EFFDATE,'yyyy-mm-dd') THEN 'Y' ELSE 'N' END
ELSE
CASE WHEN pack_osi_util.GetBusDateForward_Org(AR.DueDate + NVL(AL.GraceDays,0), 1)=To_Date(in_EFFDATE,'yyyy-mm-dd') THEN 'Y' ELSE 'N' END
END
ELSE
CASE WHEN AR.DueDate + NVL(AL.GraceDays,0) = To_Date(in_EFFDATE,'yyyy-mm-dd') THEN 'Y' ELSE 'N' END
END
) = 'Y'
/*BIBAOPING--修改对公日历时增加--End:2010-09-27*/
--AND a.acctnbr in (200000683469) -- test for NRS
GROUP BY ASA.AcctNbr,
ASA.SubAcctNbr,
ASA.BalTypCd,
AL.GraceDays
ORDER BY ASA.AcctNbr,
ASA.SubAcctNbr;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-732252/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-732252/