select b.bnk_nm as CAP_CORG_NM,a.* from (
select c.*,
( case
when c.CAP_CORG_Y like '%_DC' then SUBSTR(c.CAP_CORG_Y,0,LENGTH(c.CAP_CORG_Y)-3)
when c.CAP_CORG_Y like 'DC_%' then SUBSTR(c.CAP_CORG_Y,LENGTH(c.CAP_CORG_Y)-3,LENGTH(c.CAP_CORG_Y))
else c.CAP_CORG_Y end) CAP_CORG FROM (
SELECT substr(CAP_CORG,4,length(CAP_CORG)) as CAP_CORG_Y,USR_NO ,CRD_TYP,CAP_CRD_NO,VIR_CRD_NO AS CAP_CRD_NO1,USR_NM,MBL_NO,VIR_MBL_NO AS MBL_NO1,VIR_ID_NO AS BNK_ID_NO,TM_SMP FROM PWMTQPAG ) c
) a LEFT JOIN CMMTBKCD b on a.CAP_CORG = b.CORP_ORG
select c.*,
( case
when c.CAP_CORG_Y like '%_DC' then SUBSTR(c.CAP_CORG_Y,0,LENGTH(c.CAP_CORG_Y)-3)
when c.CAP_CORG_Y like 'DC_%' then SUBSTR(c.CAP_CORG_Y,LENGTH(c.CAP_CORG_Y)-3,LENGTH(c.CAP_CORG_Y))
else c.CAP_CORG_Y end) CAP_CORG FROM (
SELECT substr(CAP_CORG,4,length(CAP_CORG)) as CAP_CORG_Y,USR_NO ,CRD_TYP,CAP_CRD_NO,VIR_CRD_NO AS CAP_CRD_NO1,USR_NM,MBL_NO,VIR_MBL_NO AS MBL_NO1,VIR_ID_NO AS BNK_ID_NO,TM_SMP FROM PWMTQPAG ) c
) a LEFT JOIN CMMTBKCD b on a.CAP_CORG = b.CORP_ORG