DB2_V9.7迁移Gbase8a

SELECT XX FROM (SELECT XXX FROM DUAL)  ;

调整为

SELECT XX FROM (SELECT XXX FROM DUAL)  AS TMP_ALIAS_11 ;

 DECLARE EXIT HANDLER FOR SQLEXCEPTION

 BEGIN

  SET V_PROC_SQLCODE = SQLCODE;

  ROLLBACK;

  INSERT INTO DWMM.PROC_ERR_LOG

  VALUES(V_PROC_SCHEMA,V_PROC_NM,V_PROC_ACT_DT,V_PROC_STEPNUM,V_PROC_STEPDESC,V_STEP_START_TS,now(),V_PROC_SQLCODE);

  COMMIT;

  /* RESIGNAL; */

 END;

sourceStr=re.sub('SET[\s]+NLS_STRING_UNITS.*;[\s]+','',sourceStr,0,re.I|re.M)

sourceStr=re.sub('SET[\s]+CURRENT[\s]+SCHEMA.*;[\s]+','',sourceStr,0,re.I|re.M)

sourceStr=re.sub('SET[\s]+CURRENT[\s]+PATH.*;[\s]+','',sourceStr,0,re.I|re.M)

POSSTR  >  LOCATE

临时表语法：

DECLARE GLOBAL TEMPORARY TABLE >  CREATE  TEMPORARY  TABLE

窗口函数：

ROWNUMBER()  >  ROW_NUMBER()

字符转换函数:

Char() > to_char()

系统表DUAL替换：

SYSIBM.sysdummy1或者SYSIBM.DUAL  >  DUAL

Current timestamp > current_timestamp

Current datetime  > now()

Current date > current_date

+3 DAYS  >  + INTERVAL 3 DAY

+3 MONTHS > + INTERVAL 3 MONTH

+3 YEAR > + INTERVAL 3 YEAR

TIMESTAMP(CHAR(TRUNCATE(V_PROC_ACT_DT,'YY')),'00:00:00')  >>>>  CAST(concat(date_format(V_PROC_ACT_DT , '%Y-01-01'),' 00:00:00') as TIMESTAMP)

TIMESTAMP(CHAR(TRUNCATE(V_PROC_ACT_DT,'MM')),'00:00:00')  >>>>  CAST(concat(to_char(TRUNC(V_PROC_ACT_DT,'MM')),' 00:00:00') AS TIMESTAMP)

TIMESTAMP(CHAR(V_PROC_ACT_DT),'24:00:00')                 >>>>  CAST(CONCAT(DATE_ADD(DATE(COALESCE(V_PROC_ACT_DT , CURRENT_DATE)), INTERVAL 1 DAY),' 00:00:00')AS TIMESTAMP)

TIMESTAMP(CHAR(TRUNCATE(V_PROC_ACT_DT,'MM')-3 MONTH),'00:00:00') >>>> CAST(concat(to_char(TRUNC(V_PROC_ACT_DT,'MM')- INTERVAL 3 MONTH),' 00:00:00') AS TIMESTAMP)

TRUNC(V_PROC_ACT_DT,'YY')       >>>>  

 DATE(CONCAT(YEAR(V_PROC_ACT_DT) , '-01-01'))

TRUNC( V_PROC_ACT_DT,'Q')  >>>>  DATE(concat (year (V_PROC_ACT_DT) ,'-',lpad((quarter(V_PROC_ACT_DT)*3 - 2),2,'0'),'-01'))

TO_CHAR(TRUNC(V_PROC_ACT_DT,'Q'),'YYYYMMDD')    >>>>     to_char(date_format(concat (year ('2020-09-12') ,'-',lpad((quarter('2020-09-12')*3 - 2),2,'0'),'-01'),'%Y%m%d'))

1.  INT函数转换：

Int(xxx)   >  CAST (xxx AS INT)

1.  BIGINT函数转换：

Bigint(xxx) >  CAST (xxx AS BIGINT)

1.  DECIMAL函数转换：

DECIMAL(CUR_NUM_AMT/CUR_DEN_AMT,26,6) > CAST(CUR_NUM_AMT/CUR_DEN_AMT as DECIMAL(26,6))

1. DEC函数转换：

DEC(SUM(A.CD_BAL_AMT - A.LAST_YR_BAL_AMT),26,6) > CAST(SUM(A.CD_BAL_AMT - A.LAST_YR_BAL_AMT) as DECIMAL(26,6))

1. VALUE函数转换：

value(BIZ_TP_ID,-1)   >  COALESCE(BIZ_TP_ID,-1)

1. DOUBLE函数转换：

DOUBLE(A.AMT_TRANS)  >  CAST(A.AMT_TRANS AS DOUBLE)

1. DIGITS函数转换：

RIGHT(DIGITS(CAST(ROW_NUMBER() OVER(ORDER BY T.INST_DATE,T.INST_TIME,T.PAN ASC) AS BIGINT)),7) > RIGHT(CAST(ROW_NUMBER() OVER(ORDER BY T.INST_DATE,T.INST_TIME,T.PAN ASC) AS CHAR ),7)

1. FLOAT函数转换：

FLOAT(I.STD_ID)  >  CAST (I.STD_ID AS FLOAT)

1. NVL函数转换：(超过三个参数，gbase不支持，需要用COALESCE替换)

NVL(xx,xx,xxx) >  COALESCE(xx,xx,xxx)

1. MIN函数转换：(超过2个参数，gbase不支持，需要用LEAST替换)

MIN(xx,xxx,xxx)  >  LEAST(xx,xxx,xxx)

1. MAX函数转换：(超过2个参数，gbase不支持，需要用GREATEST替换)

MIN(xx,xxx,xxx)  >  GREATEST(xx,xxx,xxx)

---原始SQL

      CASE WHEN LENGTH(TRIM(T1.MCHT_NAME),CODEUNITS16) <= 3 THEN T1.MCHT_NAME

      WHEN LENGTH(SUBSTR(T1.MCHT_NAME,1,1),CODEUNITS16) = 0 AND LENGTH(SUBSTR(T1.MCHT_NAME,LENGTH(T1.MCHT_NAME)),CODEUNITS16) = 0 

      THEN SUBSTR(TRIM(T1.MCHT_NAME),1,2)||'*'||SUBSTR(T1.MCHT_NAME,LENGTH(T1.MCHT_NAME)-1)

      WHEN LENGTH(SUBSTR(T1.MCHT_NAME,1,1),CODEUNITS16) = 0 AND LENGTH(SUBSTR(T1.MCHT_NAME,LENGTH(T1.MCHT_NAME)),CODEUNITS16) = 1 

      THEN SUBSTR(TRIM(T1.MCHT_NAME),1,2)||'*'||SUBSTR(T1.MCHT_NAME,LENGTH(T1.MCHT_NAME))

      WHEN LENGTH(SUBSTR(T1.MCHT_NAME,1,1),CODEUNITS16) = 1 AND LENGTH(SUBSTR(T1.MCHT_NAME,LENGTH(T1.MCHT_NAME)),CODEUNITS16) = 0 

      THEN SUBSTR(TRIM(T1.MCHT_NAME),1,1)||'*'||SUBSTR(T1.MCHT_NAME,LENGTH(T1.MCHT_NAME)-1)

      WHEN LENGTH(SUBSTR(T1.MCHT_NAME,1,1),CODEUNITS16) = 1 AND LENGTH(SUBSTR(T1.MCHT_NAME,LENGTH(T1.MCHT_NAME)),CODEUNITS16) = 1 

      THEN SUBSTR(TRIM(T1.MCHT_NAME),1,1)||'*'||SUBSTR(T1.MCHT_NAME,LENGTH(T1.MCHT_NAME))

      ELSE NULL

      END                    AS   MCHT_NAME_TM

---改造SQL

      CASE WHEN CHAR_LENGTH(TRIM(T1.MCHT_NAME))        <= 2 THEN T1.MCHT_NAME

      ELSE LEFT(TRIM(T1.MCHT_NAME),1) || '*' || RIGHT(TRIM(T1.MCHT_NAME),1)

      END                    AS   MCHT_NAME_TM

LENGTH(TRIM(TRANSLATE(IDV_IDENT_CRD_NO,'','0123456789')),CODEUNITS32)

转换为 CHAR_LENGTH(TRIM(TRANSLATE(IDV_IDENT_CRD_NO,'','0123456789')))

    '1': 'FRAC_SECOND',

    '2': 'SECOND',

    '4': 'MINUTE',

    '8': 'HOUR',

    '16': 'DAY',

    '32': 'WEEK',

    '64': 'MONTH',

    '128': 'QUARTER',

'256': 'YEAR'

---原始sql：

TIMESTAMPDIFF(16,CHAR(TIMESTAMP(REPLACE(REPLACE(A.DEALTM,'T','-'),':','.'))-TIMESTAMP(REPLACE(REPLACE(D.MESGTM,'T','-'),':','.'))))) AS DAY_DFF --差额天数

---转换后：

TIMESTAMPDIFF(DAY,TO_DATE(REPLACE(D.MESGTM,'T',' '),'YYYY-MM-DD HH24:MI:SS'),TO_DATE(REPLACE(A.DEALTM,'T',' '),'YYYY-MM-DD HH24:MI:SS'))) AS DAY_DFF

--原始语句

FROM PBANKCIF_TMP A 

,TABLE(VALUES('D'),('M'),('Q'),('Y')) AS B(FREQUENCY)

--改造后

FROM PBANKCIF_TMP A                  

   ,(

  select 'D' as FREQUENCY from dual

  union all 

  select 'M' as FREQUENCY from dual 

  union all

  select 'Q' as FREQUENCY from dual

  union all

  select 'Y' as FREQUENCY from dual

 ) B 

--原始sql

INSERT INTO SESSION.IDVFINC_TASK_ENTRY_TMP(

             TASK_TYPE  --任务类型，存款/理财

            ,DEPT_CODE  --部门编号

            ,SEQ        --序号，代表月份

            ,BALANCE    --当月目标值

            )

     SELECT  A.TASK_TYPE   AS TASK_TYPE  --任务类型，存款/理财

            ,A.DEPT_CODE   AS DEPT_CODE  --部门编号

            ,B.SEQ         AS SEQ        --序号，代表月份

            ,NVL(B.BALANCE,0)     AS BALANCE    --当月目标值

       FROM BRCH_CCKPT.IDVFINC_TASK_ENTRY A,

       TABLE(VALUES (1,A.M_01)           --1月任务值

                   ,(2,A.M_02)           --2月任务值

                   ,(3,A.M_03)           --3月任务值

                   ,(4,A.M_04)           --4月任务值

                   ,(5,A.M_05)           --5月任务值

                   ,(6,A.M_06)           --6月任务值

                   ,(7,A.M_07)           --7月任务值

                   ,(8,A.M_08)           --8月任务值

                   ,(9,A.M_09)           --9月任务值

                   ,(10,A.M_10)          --10月任务值

                   ,(11,A.M_11)          --11月任务值

                   ,(12,A.M_12)          --12月任务值

                   ,(13,A.TOTAL_VAL)     --年度任务值

            )

            AS B(SEQ,BALANCE)

      WHERE A.DATA_YEAR = SUBSTR(V_PROC_ACT_DT,1,4)

        AND A.MANAGER_CODE = '0000'  ; --已分配额度的

--修改后：

INSERT INTO IDVFINC_TASK_ENTRY_TMP(

       TASK_TYPE -- 任务类型，存款/理财

      ,DEPT_CODE -- 部门编号

      ,SEQ    -- 序号，代表月份

      ,BALANCE  -- 当月目标值

      )

   SELECT TASK_TYPE

         ,DEPT_CODE

                 ,SEQ

                 ,BALANCE

   FROM (

              SELECT A.TASK_TYPE  AS TASK_TYPE -- 任务类型，存款/理财

                ,A.DEPT_CODE  AS DEPT_CODE -- 部门编号

                    ,A.M_01 AS M_01

                    ,A.M_02 AS M_02

                    ,A.M_03 AS M_03

                    ,A.M_04 AS M_04

                    ,A.M_05 AS M_05

                    ,A.M_06 AS M_06

                    ,A.M_07 AS M_07

                    ,A.M_08 AS M_08

                    ,A.M_09 AS M_09

                    ,A.M_10 AS M_10

                    ,A.M_11 AS M_11

                    ,A.M_12 AS M_12

                    ,A.TOTAL_VAL AS TOTAL_VAL

           FROM BRCH_CCKPT.IDVFINC_TASK_ENTRY A

          WHERE A.DATA_YEAR = SUBSTR(V_PROC_ACT_DT,1,4)

            AND A.MANAGER_CODE = '0000' -- 已分配额度的

            ) T

        UNPIVOT(

            SEQ FOR BALANCE IN (M_01 as 1, M_02 as 2, M_03 as 3, M_04 as 4, M_05 as 5, M_06 as 6, M_07 as 7, M_08 as 8, M_09 as 9, M_10 as 10, M_11 as 11, M_12 as 12, TOTAL_VAL as 13));

--原始sql

LISTAGG(BZ, ',') WITHIN GROUP (ORDER BY NCD_ISSUE_SERIAL ) AS BZ

--修改

GROUP_CONCAT(BZ ORDER BY NCD_ISSUE_SERIAL SEPARATOR ',') AS BZ

--原始 

WITH LOAN_APP_TMP   (LOAN_APP_NO,CST_ID)   AS (

 SELECT LOAN_APP_NO,

     CST_ID

 FROM  SMY.LOAN_APP_SMY

 WHERE  ACG_DT=V_PROC_ACT_DT

 AND   OCRNC_TP_ID=212300002

 AND   APP_DT>V_YI_DAY

 ),

--修改

 WITH LOAN_APP_TMP  /* (LOAN_APP_NO,CST_ID) */  AS (

 SELECT LOAN_APP_NO,

     CST_ID

 FROM  SMY.LOAN_APP_SMY

 WHERE  ACG_DT=V_PROC_ACT_DT

 AND   OCRNC_TP_ID=212300002

 AND   APP_DT>V_YI_DAY

 ),

1. db2

SELECT CHR(65) FROM SYSIBM.DUAL ;   -----'A'

1. Gbase8a

SELECT CHAR(65) FROM DUAL ;   -----'A'

1.原始语句：

MERGE INTO OMRS.FCT_RPT_50816 A

  USING (SELECT DISTINCT TRIM(SRC_CODE) AS SRC_CODE,TRIM(SRC_CODE_DESC) AS SRC_CODE_DESC

      FROM DWMM.VAL_X_CL

      WHERE SRC_FLD_NM='ZHJNZL'

     )B

  ON A.CTF_TP_ID=B.SRC_CODE

  AND A.DAY_ID=V_PROC_ACT_DT

  WHEN MATCHED THEN UPDATE SET

  A.CTF_TP_NM=B.SRC_CODE_DESC

  ;

2.改造HASH的TEMP表：

CREATE TEMPORARY TABLE omrs.merge_temp_1_2de6c238

DISTRIBUTED BY ('SRC_CODE')

AS SELECT DISTINCT TRIM(SRC_CODE) AS SRC_CODE,TRIM(SRC_CODE_DESC) AS SRC_CODE_DESC

      FROM DWMM.VAL_X_CL

      WHERE SRC_FLD_NM='ZHJNZL';

3.将TEMP表和MERGE语句结合：

MERGE INTO OMRS.FCT_RPT_50816 A

  USING  omrs.merge_temp_1_2de6c238 B

  ON A.CTF_TP_ID=B.SRC_CODE

  AND A.DAY_ID=V_PROC_ACT_DT

  WHEN MATCHED THEN UPDATE SET

  A.CTF_TP_NM=B.SRC_CODE_DESC

  ;

--原MERGE

MERGE INTO OMRS.FCT_IND_SYS T

  USING

  (SELECT

    V_PROC_ACT_DT AS DAY_ID,

    CCY_ID,

    OU_IP_ID,

    IND_ID,

    YA_AMT/V_YR_ACT_DYS AS CUR_Y_DA_AMT,

    MA_AMT/V_MTH_ACT_DYS AS CUR_M_DA_AMT

    FROM OMRS.FCT_IND_SYS

   WHERE DAY_ID = V_PROC_ACT_DT) B

   ON T.DAY_ID=B.DAY_ID AND T.CCY_ID=B.CCY_ID AND T.OU_IP_ID=B.OU_IP_ID AND T.IND_ID=B.IND_ID

   WHEN MATCHED AND T.DAY_ID =V_PROC_ACT_DT

     AND EXISTS(SELECT 1

         FROM OMRS.DIM_IND_SYS B

         WHERE CLCN_TP_ID IN( 990130004,990130007 )  -- 加减值，均值汇总

          AND DATA_UPD_F = 'Y'       -- 数据更新标志：仅取需要更新数据的指标

          AND CLCN_F = 'Y'        -- 指标计算标志：Y表示计算，N表示仅作指标管理

          AND V_PROC_ACT_DT >= STRT_DT

          AND V_PROC_ACT_DT < END_DT

          AND T.IND_ID=B.IND_ID )

   THEN UPDATE SET

   T.CUR_Y_DA_AMT=B.CUR_Y_DA_AMT,

   T.CUR_M_DA_AMT=B.CUR_M_DA_AMT;

--修改

MERGE INTO OMRS.FCT_IND_SYS T

  USING

  (SELECT

    V_PROC_ACT_DT AS DAY_ID,

    F.CCY_ID,

    F.OU_IP_ID,

    F.IND_ID,

    F.YA_AMT/V_YR_ACT_DYS AS CUR_Y_DA_AMT,

    F.MA_AMT/V_MTH_ACT_DYS AS CUR_M_DA_AMT

    FROM OMRS.FCT_IND_SYS F

 INNER JOIN OMRS.DIM_IND_SYS D

       ON  F.IND_ID=D.IND_ID

       AND CLCN_TP_ID IN( 990130004,990130007 )  -- 加减值，均值汇总

          AND DATA_UPD_F = 'Y'       -- 数据更新标志：仅取需要更新数据的指标

          AND CLCN_F = 'Y'        -- 指标计算标志：Y表示计算，N表示仅作指标管理

          AND V_PROC_ACT_DT >= D.STRT_DT

          AND V_PROC_ACT_DT < D.END_DT

   WHERE F.DAY_ID =V_PROC_ACT_DT ) B

   ON T.DAY_ID=B.DAY_ID AND T.CCY_ID=B.CCY_ID AND T.OU_IP_ID=B.OU_IP_ID AND T.IND_ID=B.IND_ID

   WHEN MATCHED 

   THEN UPDATE SET

   T.CUR_Y_DA_AMT=B.CUR_Y_DA_AMT,

   T.CUR_M_DA_AMT=B.CUR_M_DA_AMT;

--原MERGE

MERGE INTO OMRS.FCT_IND_SYS T

  USING

  (SELECT

    V_PROC_ACT_DT AS DAY_ID ,

    CCY_ID,

    OU_IP_ID,

    IND_ID,

    CUR_AMT AS CP_AMT,

    YI_AMT AS CP_YI_AMT,

    YI_Y_DA_AMT AS CP_YI_Y_DA_AMT,

    CUR_Y_DA_AMT AS CP_Y_DA_AMT,

    YI_M_DA_AMT AS CP_YI_M_DA_AMT,

    CUR_M_DA_AMT AS CP_M_DA_AMT

    FROM OMRS.FCT_IND_SYS

   WHERE DAY_ID = V_PROC_ACT_DT- INTERVAL 1 YEAR ) B

   ON T.DAY_ID=B.DAY_ID AND T.CCY_ID=B.CCY_ID AND T.OU_IP_ID=B.OU_IP_ID AND T.IND_ID=B.IND_ID

   WHEN MATCHED AND T.DAY_ID =V_PROC_ACT_DT

      AND EXISTS

        (SELECT

        1

       FROM OMRS.DIM_IND_SYS B

       WHERE CLCN_TP_ID IN( 990130004,990130007 )  -- 加减值，均值汇总

        AND DATA_UPD_F = 'Y'       -- 数据更新标志：仅取需要更新数据的指标

        AND CLCN_F = 'Y'        -- 指标计算标志：Y表示计算，N表示仅作指标管理

        AND V_PROC_ACT_DT >= STRT_DT

        AND V_PROC_ACT_DT < END_DT

        AND T.IND_ID=B.IND_ID )

   THEN UPDATE SET

   T.CP_AMT=B.CP_AMT,

   T.CP_YI_AMT=B.CP_YI_AMT,

   T.CP_YI_Y_DA_AMT=B.CP_YI_Y_DA_AMT,

   T.CP_Y_DA_AMT=B.CP_Y_DA_AMT,

   T.CP_YI_M_DA_AMT=B.CP_YI_M_DA_AMT,

   T.CP_M_DA_AMT=B.CP_M_DA_AMT

   ;*/

--修改

MERGE INTO OMRS.FCT_IND_SYS T

  USING

  (SELECT

    V_PROC_ACT_DT AS DAY_ID ,

    F.CCY_ID,

    F.OU_IP_ID,

    F.IND_ID,

    F.CUR_AMT AS CP_AMT,

    F.YI_AMT AS CP_YI_AMT,

    F.YI_Y_DA_AMT AS CP_YI_Y_DA_AMT,

    F.CUR_Y_DA_AMT AS CP_Y_DA_AMT,

    F.YI_M_DA_AMT AS CP_YI_M_DA_AMT,

    F.CUR_M_DA_AMT AS CP_M_DA_AMT

    FROM OMRS.FCT_IND_SYS F

 INNER JOIN OMRS.DIM_IND_SYS D

 ON F.IND_ID=D.IND_ID

 AND D.CLCN_TP_ID IN( 990130004,990130007 )  -- 加减值，均值汇总

    AND D.DATA_UPD_F = 'Y'       -- 数据更新标志：仅取需要更新数据的指标

    AND D.CLCN_F = 'Y'        -- 指标计算标志：Y表示计算，N表示仅作指标管理

    AND V_PROC_ACT_DT >= D.STRT_DT

    AND V_PROC_ACT_DT < D.END_DT

 WHERE F.DAY_ID =V_PROC_ACT_DT

 AND F.DAY_ID = DATE_SUB(V_PROC_ACT_DT, INTERVAL 1 YEAR) B

 ON T.DAY_ID=B.DAY_ID AND T.CCY_ID=B.CCY_ID AND T.OU_IP_ID=B.OU_IP_ID AND T.IND_ID=B.IND_ID

   WHEN MATCHED

   THEN UPDATE SET

   T.CP_AMT=B.CP_AMT,

   T.CP_YI_AMT=B.CP_YI_AMT,

   T.CP_YI_Y_DA_AMT=B.CP_YI_Y_DA_AMT,

   T.CP_Y_DA_AMT=B.CP_Y_DA_AMT,

   T.CP_YI_M_DA_AMT=B.CP_YI_M_DA_AMT,

   T.CP_M_DA_AMT=B.CP_M_DA_AMT

   ;

--原MERGE，三表不同 

MERGE INTO OMRS.FCT_IND_SYS T

   USING

   (

   SELECT OU_IP_ID,OU_LVL_ID, OU_AHR_DSG_TP_ID FROM OMRS.DIM_OU_DTL

   WHERE V_PROC_ACT_DT >=EFF_DT AND V_PROC_ACT_DT

   ) B

   ON T.OU_IP_ID=B.OU_IP_ID

   WHEN MATCHED AND T.DAY_ID = V_PROC_ACT_DT

   AND EXISTS

        (SELECT

        1

       FROM OMRS.DIM_IND_SYS B

       WHERE CLCN_TP_ID IN( 990130004,990130007 )  -- 加减值，均值汇总

        AND DATA_UPD_F = 'Y'       -- 数据更新标志：仅取需要更新数据的指标

        AND CLCN_F = 'Y'        -- 指标计算标志：Y表示计算，N表示仅作指标管理

        AND V_PROC_ACT_DT >= STRT_DT

        AND V_PROC_ACT_DT < END_DT

        AND T.IND_ID=B.IND_ID )

   THEN

   UPDATE SET

   T.OU_LVL_ID=B.OU_LVL_ID,

   T.OU_AHR_DSG_TP_ID=B.OU_AHR_DSG_TP_ID

   ;*/

--修改

MERGE INTO OMRS.FCT_IND_SYS T

  USING

  (SELECT D.OU_IP_ID,D.OU_LVL_ID, D.OU_AHR_DSG_TP_ID,I.IND_ID FROM OMRS.DIM_OU_DTL D

   INNER JOIN OMRS.DIM_IND_SYS I

   ON I.CLCN_TP_ID IN( 990130004,990130007 )  -- 加减值，均值汇总

        AND I.DATA_UPD_F = 'Y'       -- 数据更新标志：仅取需要更新数据的指标

        AND I.CLCN_F = 'Y'        -- 指标计算标志：Y表示计算，N表示仅作指标管理

        AND V_PROC_ACT_DT >= I.STRT_DT

        AND V_PROC_ACT_DT < I.END_DT

   WHERE V_PROC_ACT_DT >=D.EFF_DT AND V_PROC_ACT_DT

    ON T.OU_IP_ID=B.OU_IP_ID

 AND T.IND_ID=B.IND_ID

    AND T.DAY_ID = V_PROC_ACT_DT

 WHEN MATCHED 

 THEN

   UPDATE SET

   T.OU_LVL_ID=B.OU_LVL_ID,

   T.OU_AHR_DSG_TP_ID=B.OU_AHR_DSG_TP_ID

   ;

--原MERGE，三表不同

MERGE INTO OMRS.FCT_IND_SYS T

 USING(

     SELECT

       A.OU_IP_ID             AS OU_IP_ID

       ,A.OU_LVL_ID            AS OU_LVL_ID

       ,A.OU_AHR_DSG_TP_ID         AS OU_AHR_DSG_TP_ID

     FROM OMRS.DIM_OU_DTL A

     WHERE V_PROC_ACT_DT >= A.EFF_DT AND V_PROC_ACT_DT < A.END_DT

 ) T2

 ON T.OU_IP_ID=T2.OU_IP_ID

 WHEN MATCHED AND T.DAY_ID =V_PROC_ACT_DT

        AND EXISTS(SELECT 1 FROM TMP_CFG_IND_X_AR_DISTINCT_IND T3 WHERE T.IND_ID=T3.IND_ID )

 THEN UPDATE SET

         T.OU_LVL_ID    = T2.OU_LVL_ID,

         T.OU_AHR_DSG_TP_ID = T2.OU_AHR_DSG_TP_ID

--修改

MERGE INTO OMRS.FCT_IND_SYS T

 USING(

     SELECT

       A.OU_IP_ID             AS OU_IP_ID

       ,A.OU_LVL_ID            AS OU_LVL_ID

       ,A.OU_AHR_DSG_TP_ID         AS OU_AHR_DSG_TP_ID

     FROM OMRS.DIM_OU_DTL A

  INNER JOIN OMRS.FCT_IND_SYS F

        ON A.OU_IP_ID=F.OU_IP_ID

  INNER JOIN TMP_CFG_IND_X_AR_DISTINCT_IND TM

        ON F.IND_ID = TM.IND_ID

     WHERE V_PROC_ACT_DT >= A.EFF_DT

  AND V_PROC_ACT_DT < A.END_DT

  AND F.DAY_ID =V_PROC_ACT_DT

 ) T2

 ON T.OU_IP_ID=T2.OU_IP_ID

 WHEN MATCHED

 THEN UPDATE SET

         T.OU_LVL_ID    = T2.OU_LVL_ID,

         T.OU_AHR_DSG_TP_ID = T2.OU_AHR_DSG_TP_ID;