Hi,
In the below statement in a view:
AND EXISTS
(SELECT 'X'
FROM cc_entity b
WHERE b.krs_cd = a.org_cd);
a.org_cd is getting the value from the below update statement. How can I embed this condition to avoid update statement and achieve the same in the above condition by replacing a.org_cd with the below update condition:
can I rewrite as below:
AND EXISTS
(SELECT 'X'
FROM cc_entity b
WHERE b.krs_cd =
(SELECT (CASE
WHEN EXISTS
(SELECT 1
FROM tab_ptr tabptr
WHERE tabptr.ptr = j.ptr_cd
AND tabptr.flag = 'NAC')
THEN
(SELECT *
FROM (SELECT DISTINCT org_cd
FROM c_tab_sat c
WHERE c.mtl = j.mtl_cd
AND c.csx_org IN
('A1',
'B1',
'E1',
'G1',
'S1')
AND c.act_cd IS NOT NULL)
WHERE ROWNUM = 1)
END)
AS org_cd);
Update--Statement:
/* Formatted on 8/1/2017 3:39:00 PM (QP5 v5.215.12089.38647) */
UPDATE table_stg j
SET j.org_cd =
(SELECT *
FROM (SELECT DISTINCT org_cd
FROM c_tab_sat c
WHERE c.mtl = j.mtl_cd
AND c.csx_org IN ('A1', 'B1', 'E1', 'G1', 'S1')
AND c.act_cd IS NOT NULL)
WHERE ROWNUM = 1)
WHERE EXISTS
(SELECT 1
FROM tab_ptr tabptr
WHERE tabptr.ptr = j.ptr_cd AND tabptr.flag = 'NAC');
Thanks