Alternative to sql NOT IN?
- by Alex
Hi,
I am trying to make a materialized view in Oracle (I am a newbie, btw). For some reason, it doesn't like the presence of sub-query in it. I've been trying to use LEFT OUTER JOIN instead, but it's returning different data set now.
Put simply, here's the code I'm trying to modify:
SELECT *
FROM table1 ros, table2 bal, table3 flx
WHERE flx.name = 'XXX'
AND flx.value = bal.value
AND NVL (ros.ret, 'D') = Nvl (flx.attr16, 'D')
AND ros.value = bal.segment3
AND ros.type IN ( 'AL', 'AS', 'PL' )
AND bal.period = 13
AND bal.code NOT IN (SELECT bal1.code
FROM table2 bal1
WHERE bal1.value = flx.value
AND bal1.segment3 = ros.value
AND bal1.flag = bal.flag
AND bal1.period = 12
AND bal1.year = bal.year)
And here's one of my attempt:
SELECT *
FROM table1 ros, table2 bal, table3 flx
LEFT OUTER JOIN table2 bal1
ON bal.code = bal1.code
WHERE bal1.code is null
AND bal1.segment3 = ros.value
AND bal.segment3 = ros.value
AND bal1.flag = bal.flag
AND bal1.year = bal.year
AND flx.name = 'XXX'
AND flx.value = bal.value
AND bal1.value = flx.value
AND bal1.period_num = 12
AND NVL (ros.type, 'D') = NVL (flx.attr16, 'D')
AND ros.value = bal.segment3
AND ros.type IN ( 'AL', 'AS', 'PL' )
AND bal.period = 13;
This drives me nuts! Thanks in advance for the help :)
