SELECT *
FROM LNSVCLIB.MAPMEMBER


UPDATE LNSVCLIB.MAPMEMBER A SET INVT_AEMP_TPCD3=(
SELECT CDDT
FROM TB_SY1007M B
WHERE A.INVT_AEMP_TPCD3=B.DSCP
AND B.CDTP='INVT_AEMP_TPCD'
)

SELECT *
FROM Tb_IL1014M

SELECT *
FROM TB_SY1007M
WHERE CDTP='INVT_AEMP_TPCD'


SELECT *
FROM TB_IL1044D

INSERT INTO TB_IL1044D (
        CNTRNO                    --계약번호
       ,EXE_DGRN                  --실행차수
       ,CUST_NO                   --고객번호
       ,STD_SDT                   --기준시작일자
       ,CROF_TPCD                 --심사역구분코드
       ,INVT_AEMP_TPCD            --투자담당자구분코드
       ,CROF_EMPNO                --심사역사원번호
       ,STD_EDT                   --기준종료일자
       ,CTBD_RATE                 --기여도
       ,REG_DTM                   --등록일시
       ,REG_EMPNO                 --등록사원번호
       ,REG_IP                    --등록IP
       ,MODY_DTM                  --수정일시
       ,MODY_EMPNO                --수정사원번호
       ,MODY_IP                   --수정IP
       ,CHG_RSN                   --담당자변경사유
)
SELECT A.*
FROM 
(
SELECT    CNTRNO
,         '1'       AS EXE_DGRN
,         CUST_NO
,         REPLACE(STD_DT,'-') AS STD_SDT
,         DECODE(INVT_AEMP_TPCD,'EMP_CUST_NO1','1','EMP_CUST_NO2','2','EMP_CUST_NO3','3','') AS CROF_TPCD
,         CASE WHEN INVT_AEMP_TPCD ='EMP_CUST_NO1' 
               THEN INVT_AEMP_TPCD1 
               WHEN INVT_AEMP_TPCD='EMP_CUST_NO2' 
               THEN INVT_AEMP_TPCD2
               WHEN INVT_AEMP_TPCD='EMP_CUST_NO3' 
               THEN INVT_AEMP_TPCD3
          END AS INVT_AEMP_TPCD
,         EMP_CUST_NO     
,         '39991231' AS STD_EDT
,         CASE WHEN INVT_AEMP_TPCD ='EMP_CUST_NO1' 
               THEN DECODE(RATE1,0,'50',1,'100','') 
               WHEN INVT_AEMP_TPCD='EMP_CUST_NO2' 
               THEN DECODE(RATE2,0,'50',1,'100','')
               WHEN INVT_AEMP_TPCD='EMP_CUST_NO3' 
               THEN DECODE(RATE3,0,'50',1,'100','')
          END AS RATE
,         SYSDATE AS REG_DTM
,         'MIG'      AS REG_EMPNO
,         '127.0.0.1' AS REG_IP
,         SYSDATE AS MODY_DTM
,         'MIG' AS MODY_EMPNO
,         '127.0.0.1' AS MODY_IP
,         BIGO AS CHG_RSN
FROM MAPMEMBER
UNPIVOT(EMP_CUST_NO FOR INVT_AEMP_TPCD IN (EMP_CUST_NO3,EMP_CUST_NO1,EMP_CUST_NO2))
ORDER BY 1,2,3,4,5,6,7
)A
WHERE 'X' NOT IN (SELECT 'X'
                    FROM TB_IL1044D B
                    WHERE A.CNTRNO=B.CNTRNO
                    AND  A.EXE_DGRN=B.EXE_DGRN
                    AND A.CUST_NO=B.CUST_NO
                    AND A.STD_SDT=B.STD_SDT
                    AND A.CROF_TPCD=B.CROF_TPCD
                    AND A.INVT_AEMP_TPCD=B.INVT_AEMP_TPCD
                    )
                    

SELECT *
FROM MAPMEMBER
WHERE INVT_AEMP_TPCD1 IS NULL
