이번에 짠 지급파일 쿼리 ㅡㅡ;
UNION을 이용하여 조잡하게 작성했다.하지만 어쩔것인가 ? 실력이 미진한데.푸핫
SELECT distinct C.GRP_PYNO, E.CMPN_NM, g.cntt_dt, g.sys_dvcd, A.POLY_NO,E.NM, E.SSN,c.entr_dt, c.sys_jndt, c.stdd_pay, c.rtpy_esat, A.PAYM_MDCD,
A.BNFC_NM,A.SSN, A.JNMN_RLCD, A.DPSR_NM, A.BKCD, A.ACCT_NO, c.reti_dt,c.rtmt_rscd, H.SALE_DT, f.real_pyat, F.ICMX, F.RSTX, f.real_pyat+F.ICMX+F.RSTX
,DECODE(F.PAYM_RSCD,'0110','정년퇴직','0120','중도퇴직','0130','사망퇴직','0150','통산','0160','무급부퇴직','0210','연금'
,'0220','연금일시금','0300','운용방법매도변경','0310','중도인출개인분','0411','가입자이차배당','0412','가입자비차배당','0419','가입자배당금'
,'0421','사업자이차배당','0429','사업자배당금','0510','사망보험금','0520','상해보험금','0610','초과적립금'
,'0720','일부제도이전','0910','초과환급금','1010','중도인출단체분(천재지변)','1020','중도인출단체분(6개월이상요양)'
,'1030','중도인출단체분(주택구입)','1040','중도인출단체분(기타)','1110','약관대출','1120','약관대출전액상환','1130','약관대출일부상환'
,'1140','약관대출이자납입','1210','압류추심','9999','부담금반송')
FROM TB_OM217 A, TB_OM100 C, TB_OM101 E, tb_om237 f, tb_om114 g,tb_om216 H
WHERE A.DMND_RECP_NO = F.DMND_RECP_NO
AND A.DMND_RECP_NO = H.DMND_RECP_NO
AND A.POLY_NO = C.JNMN_PYNO
AND C.PRSN_CUST_ID = E.PRSN_CUST_ID
and A.DMND_RECP_NO = f.DMND_RECP_NO
and f.grp_pyno = g.grp_pyno
and f.paym_rscd not in ('0140')
and f.paym_rscd not in ('0810')
and H.sale_dt >= '20071001'
and H.sale_dt <= '20071031'
UNION
SELECT distinct C.GRP_PYNO, E.CMPN_NM, g.cntt_dt, g.sys_dvcd, A.POLY_NO,E.NM, E.SSN,c.entr_dt, c.sys_jndt, c.stdd_pay, c.rtpy_esat, A.PAYM_MDCD,
A.BNFC_NM,A.SSN, A.JNMN_RLCD, A.DPSR_NM, A.BKCD, A.ACCT_NO, c.reti_dt,c.rtmt_rscd, H.SALE_DT, f.real_pyat, F.ICMX, F.RSTX, I.real_pyat real_pt
,DECODE(F.PAYM_RSCD,'0140','사용자반환금')
FROM TB_OM217 A, TB_OM100 C, TB_OM101 E, tb_om237 f, tb_om114 g,tb_om216 H,tb_om230 I
WHERE A.DMND_RECP_NO = F.DMND_RECP_NO
AND A.DMND_RECP_NO = H.DMND_RECP_NO
AND A.DMND_RECP_NO = I.DMND_RECP_NO
AND A.POLY_NO = C.JNMN_PYNO
AND C.PRSN_CUST_ID = E.PRSN_CUST_ID
and A.DMND_RECP_NO = f.DMND_RECP_NO
and f.grp_pyno = g.grp_pyno
and f.paym_rscd = '0140'
and H.sale_dt >= '20071001'
and H.sale_dt <= '20071031'
UNION
SELECT distinct C.GRP_PYNO, E.CMPN_NM, g.cntt_dt, g.sys_dvcd, A.POLY_NO,E.NM, E.SSN,c.entr_dt, c.sys_jndt, c.stdd_pay, c.rtpy_esat, A.PAYM_MDCD,
A.BNFC_NM,A.SSN, A.JNMN_RLCD, A.DPSR_NM, A.BKCD, A.ACCT_NO, c.reti_dt,c.rtmt_rscd, H.SALE_DT, f.real_pyat, F.ICMX, F.RSTX, f.real_pyat+F.ICMX+F.RSTX
,DECODE(F.PAYM_RSCD,'0710','전체제도이전')
FROM TB_OM217 A, TB_OM100 C, TB_OM101 E, tb_om237 f, tb_om114 g,tb_om216 H
WHERE A.DMND_RECP_NO = F.DMND_RECP_NO
AND A.DMND_RECP_NO = H.DMND_RECP_NO
AND A.POLY_NO = C.GRP_PYNO
AND C.PRSN_CUST_ID = E.PRSN_CUST_ID
and A.DMND_RECP_NO = f.DMND_RECP_NO
and f.grp_pyno = g.grp_pyno
and f.paym_rscd = '0710'
and H.sale_dt >= '20071001'
and H.sale_dt <= '20071031'
UNION
SELECT distinct C.GRP_PYNO, E.CMPN_NM, g.cntt_dt, g.sys_dvcd, A.POLY_NO,E.NM, E.SSN,c.entr_dt, c.sys_jndt, c.stdd_pay, c.rtpy_esat, A.PAYM_MDCD,
A.BNFC_NM,A.SSN, A.JNMN_RLCD, A.DPSR_NM, A.BKCD, A.ACCT_NO, c.reti_dt,c.rtmt_rscd, H.SALE_DT, f.real_pyat, F.ICMX, F.RSTX, I.real_pyat
,DECODE(F.PAYM_RSCD,'0810','해약(제도폐지/중단)')
FROM TB_OM217 A, TB_OM100 C, TB_OM101 E, tb_om237 f, tb_om114 g,tb_om216 H, tb_om230 I
WHERE A.DMND_RECP_NO = F.DMND_RECP_NO
AND A.DMND_RECP_NO = H.DMND_RECP_NO
AND A.DMND_RECP_NO = I.DMND_RECP_NO
AND A.POLY_NO = C.JNMN_PYNO
--AND I.jnmn_pyno = c.JNMN_PYNO
AND E.NM = I.DPSR_NM
AND C.PRSN_CUST_ID = E.PRSN_CUST_ID
and f.grp_pyno = g.grp_pyno
and f.paym_rscd = '0810'
and H.sale_dt >= '20071001'
and H.sale_dt <= '20071031'
Posted by 최정민
