I have a query where I have to remove the WITH clauses and it still have to return the same results. One of the clauses should go in to Join I guess and the other one in having ? Am I in a right direction...

Any suggestion

 with  QA_m as( 
    select adr_id, max(eff_ts)as EFF_TS 
    from addr group by adr_id)
    ,
    QA_address as(select q.adr_id,q.EFF_TS,d.COUNTRY_ID,d.city,d.POST_CODE,d.STREET,d.UNIT_NBR,d.ADL_INFO
     from QA_m q join ADDR d
     on q.adr_id=d.adr_id and q.EFF_TS=d.EFF_TS)
     
    select
        c.SRGT_KEY_VAL as Customer_id, 
        CAST(i.EFF_TS as date) as "EFF_DT",
        i.EFF_TS,
        '9999-12-31' as END_DT,
        'N' as DEL_IND,
        'I' as CUSTOMER_TYPE,
        case when ctr.NAME = 'Canada' then 'Y'
                 else 'N' 
                     END as RESIDENCE_FLAG,
        NVL(ctr.name,'N/A') as country,
        NVL((trim(TITLE) ||' '||trim(FIRST_NAME)||' '||trim(LAST_NAME)),' ') as NAME,
        NVL((trim(CITY)||' '||trim(POST_CODE)||' '||trim(STREET)||' '||trim(UNIT_NBR)||' '||trim(ADL_INFO)),' ') as ADDRESS,
        case when i.BIRTH_DATE=TO_date('9999-12-31') then NULL
                 else TRUNC(months_between(sysdate, i.BIRTH_DATE) / 12) 
                     end AGE,
        case when substr(GENDER,1,1) = 'M' then 'M'
             when substr(GENDER,1,1) = 'm' then 'M'
             when substr(GENDER,1,1) = 'F' then 'F'
             when substr(GENDER,1,1) = 'f' then 'F'
                 else NULL 
                     end as GENDER,
        NULL as VAT_NUMBER,
        NULL as BRANCH,
        NULL as EMPLOYEES                                      
    from IDV i 
        join CSTMR_SRGT_KEY c
            on i.IDV_ID=c.ntrl_key_val 
        left join QA_address B
            on i.adr_ID=b.adr_id 
       left join COUNTRY ctr
            on ctr.COUNTRY_ID=b.COUNTRY_ID
    where SRC_STM_ID = 100
        and i.END_TS='9999-12-31 23:59:59.999999000'  
        and i.DEL_IND='N';

CodePudding user response：

These are subqueries, so - just put them into appropriate places (see comments which indicate that):

SELECT c.srgt_key_val AS customer_id,
       CAST (i.eff_ts AS DATE) AS "EFF_DT",
       i.eff_ts,
       '9999-12-31' AS end_dt,
       'N' AS del_ind,
       'I' AS customer_type,
       CASE WHEN ctr.name = 'Canada' THEN 'Y' ELSE 'N' END AS residence_flag,
       NVL (ctr.name, 'N/A') AS country,
       NVL (
          (   TRIM (title)
           || ' '
           || TRIM (first_name)
           || ' '
           || TRIM (last_name)),
          ' ') AS name,
       NVL (
          (   TRIM (city)
           || ' '
           || TRIM (post_code)
           || ' '
           || TRIM (street)
           || ' '
           || TRIM (unit_nbr)
           || ' '
           || TRIM (adl_info)),
          ' ') AS address,
       CASE
          WHEN i.birth_date = TO_DATE ('9999-12-31') THEN NULL
          ELSE TRUNC (MONTHS_BETWEEN (SYSDATE, i.birth_date) / 12)
       END age,
       CASE
          WHEN SUBSTR (gender, 1, 1) = 'M' THEN 'M'
          WHEN SUBSTR (gender, 1, 1) = 'm' THEN 'M'
          WHEN SUBSTR (gender, 1, 1) = 'F' THEN 'F'
          WHEN SUBSTR (gender, 1, 1) = 'f' THEN 'F'
          ELSE NULL
       END AS gender,
       NULL AS vat_number,
       NULL AS branch,
       NULL AS employees
  FROM idv i
       JOIN cstmr_srgt_key c ON i.idv_id = c.ntrl_key_val
       LEFT JOIN (                                   --> this is QA_address
                  SELECT q.adr_id,
                         q.eff_ts,
                         d.country_id,
                         d.city,
                         d.post_code,
                         d.street,
                         d.unit_nbr,
                         d.adl_info
                    FROM (                           --> this is QA_m  
                          SELECT adr_id, MAX (eff_ts) AS eff_ts
                              FROM addr
                          GROUP BY adr_id) q
                         JOIN addr d
                            ON     q.adr_id = d.adr_id
                               AND q.eff_ts = d.eff_ts) b
          ON i.adr_id = b.adr_id
       LEFT JOIN country ctr ON ctr.country_id = b.country_id
 WHERE     src_stm_id = 100
       AND i.end_ts = '9999-12-31 23:59:59.999999000'
       AND i.del_ind = 'N';