CTE with Merge statement in Oracle-CodePudding

I am trying to create a CTE to use the same in Merge statement in Oracle, but facing error so please have a look and help me out, earlier we were using subquery instead CTE but to enhance the response time of query I am trying CTE so please suggest me another approach to enhance the response time of query. Curious to know whether oracle supports CTE and Merge statement together as I did in below code.

    With TRANS_HIST 
As 
(Select 
        NUMERO_DE_CUENTA,
        TRANS_DATETIME,
        Lag(TRANS_DATETIME, 1) 
        over 
         (
          ORDER BY NUMERO_DE_CUENTA,TRANS_DATETIME) lag_trans_datetime 
          FROM db_fraud_bpd.tbl_event_new_transaction_h
         ) 
MERGE 
INTO DB_FRAUD_BPD.TBL_RT_FEATURES_TEMP t1
USING 
(
SELECT 
        RTTEMP.ACCOUNT_NUMBER,
        RTTEMP.TRANS_DATETIME,
        CASE WHEN STDDEV(TRANS_HIST.TRANS_DATETIME - TRANS_HIST.lag_trans_datetime) = 0 THEN NULL 
             WHEN ROUND(( ( (RTTEMP.TRANS_DATETIME - Max(TRANS_HIST.TRANS_DATETIME)) - Avg(TRANS_HIST.TRANS_DATETIME - TRANS_HIST.lag_trans_datetime)) / STDDEV(TRANS_HIST.TRANS_DATETIME -TRANS_HIST.lag_trans_datetime)),3) >999999999999999 THEN 999999999999999 
             WHEN ROUND(( ( (RTTEMP.TRANS_DATETIME - Max(TRANS_HIST.TRANS_DATETIME)) - Avg(TRANS_HIST.TRANS_DATETIME - TRANS_HIST.lag_trans_datetime)) / STDDEV(TRANS_HIST.TRANS_DATETIME -TRANS_HIST.lag_trans_datetime)),3) <-99999999999999 THEN -99999999999999 
             ELSE ROUND(( ( (RTTEMP.TRANS_DATETIME - Max(TRANS_HIST.TRANS_DATETIME)) - Avg(TRANS_HIST.TRANS_DATETIME - TRANS_HIST.lag_trans_datetime)) / STDDEV(TRANS_HIST.TRANS_DATETIME -TRANS_HIST.lag_trans_datetime)),3) 
             END AS TIME_DELTA_ZSCORE_PAST_90_DAYS 
             FROM  TRANS_HIST
             right outer join 
                                 db_fraud_bpd.tbl_rt_features_temp RTTEMP 
                                 ON Cast(RTTEMP.account_number AS INTEGER) = Cast(TRANS_HIST.NUMERO_DE_CUENTA AS INTEGER) 
                            WHERE 
                            (
                            TRANS_HIST.TRANS_DATETIME      <   RTTEMP.TRANS_DATETIME 
                            AND TRANS_HIST.TRANS_DATETIME  >= (RTTEMP.TRANS_DATETIME-90)
                            ) 
                            or  TRANS_HIST.TRANS_DATETIME IS NULL 
                                 GROUP BY 
                                 RTTEMP.account_number,
                                 RTTEMP.TRANS_DATETIME
                            )TEMP 
                            ON (t1.TRANS_DATETIME=TEMP.TRANS_DATETIME AND t1.ACCOUNT_NUMBER=TEMP.ACCOUNT_NUMBER) 
                            WHEN MATCHED 
                            THEN UPDATE 
                            SET t1.TIME_DELTA_ZSCORE_PAST_90_DAYS = TEMP.TIME_DELTA_ZSCORE_PAST_90_DAYS

CodePudding user response：

Don't use TRANS_HIST as a CTE, but as a subquery.

MERGE INTO DB_FRAUD_BPD.TBL_RT_FEATURES_TEMP t1
     USING (  SELECT RTTEMP.ACCOUNT_NUMBER,
                     RTTEMP.TRANS_DATETIME,
                     CASE
                        WHEN STDDEV (
                                  TRANS_HIST.TRANS_DATETIME
                                - TRANS_HIST.lag_trans_datetime) =
                             0
                        THEN
                           NULL
                        WHEN ROUND (
                                (  (  (  RTTEMP.TRANS_DATETIME
                                       - MAX (TRANS_HIST.TRANS_DATETIME))
                                    - AVG (
                                           TRANS_HIST.TRANS_DATETIME
                                         - TRANS_HIST.lag_trans_datetime))
                                 / STDDEV (
                                        TRANS_HIST.TRANS_DATETIME
                                      - TRANS_HIST.lag_trans_datetime)),
                                3) >
                             999999999999999
                        THEN
                           999999999999999
                        WHEN ROUND (
                                (  (  (  RTTEMP.TRANS_DATETIME
                                       - MAX (TRANS_HIST.TRANS_DATETIME))
                                    - AVG (
                                           TRANS_HIST.TRANS_DATETIME
                                         - TRANS_HIST.lag_trans_datetime))
                                 / STDDEV (
                                        TRANS_HIST.TRANS_DATETIME
                                      - TRANS_HIST.lag_trans_datetime)),
                                3) <
                             -99999999999999
                        THEN
                           -99999999999999
                        ELSE
                           ROUND (
                              (  (  (  RTTEMP.TRANS_DATETIME
                                     - MAX (TRANS_HIST.TRANS_DATETIME))
                                  - AVG (
                                         TRANS_HIST.TRANS_DATETIME
                                       - TRANS_HIST.lag_trans_datetime))
                               / STDDEV (
                                      TRANS_HIST.TRANS_DATETIME
                                    - TRANS_HIST.lag_trans_datetime)),
                              3)
                     END AS TIME_DELTA_ZSCORE_PAST_90_DAYS
                FROM (SELECT NUMERO_DE_CUENTA,
                             TRANS_DATETIME,
                             LAG (TRANS_DATETIME, 1)
                                OVER (
                                   ORDER BY NUMERO_DE_CUENTA, TRANS_DATETIME) lag_trans_datetime
                        FROM db_fraud_bpd.tbl_event_new_transaction_h)
                     TRANS_HIST
                     RIGHT OUTER JOIN db_fraud_bpd.tbl_rt_features_temp RTTEMP
                        ON CAST (RTTEMP.account_number AS INTEGER) =
                           CAST (TRANS_HIST.NUMERO_DE_CUENTA AS INTEGER)
               WHERE    (    TRANS_HIST.TRANS_DATETIME < RTTEMP.TRANS_DATETIME
                         AND TRANS_HIST.TRANS_DATETIME >=
                             (RTTEMP.TRANS_DATETIME - 90))
                     OR TRANS_HIST.TRANS_DATETIME IS NULL
            GROUP BY RTTEMP.account_number, RTTEMP.TRANS_DATETIME) TEMP
        ON (    t1.TRANS_DATETIME = TEMP.TRANS_DATETIME
            AND t1.ACCOUNT_NUMBER = TEMP.ACCOUNT_NUMBER)
WHEN MATCHED
THEN
   UPDATE SET
      t1.TIME_DELTA_ZSCORE_PAST_90_DAYS = TEMP.TIME_DELTA_ZSCORE_PAST_90_DAYS

CodePudding user response：

You may use CTE in the merge statement, but in the rigth position in the merge subquery.

See example below

merge into tab
  using (with t as (
               select 1 id, 'x' x from dual union all
               select 2 id, 'y' x from dual)
        select * from t) b
  on (tab.id = b.id)
 when matched then
   update set tab.x = b.x
 when not matched then
  insert (id, x)
  values (b.id, b.x);