如何删除WITH子句Oracle查询

Question

我有一个查询，其中我必须删除WITH子句，但它仍然必须返回相同的结果。我想其中一个条款应该放在Join中，另一个应该放在have中？我的方向是正确的吗。。。

任何建议

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';

Answer 1

这些是子查询，所以-只需将它们放在适当的位置(请参阅注释(：

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';