Alternative to sql NOT IN?

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 :)

Try NOT EXISTS insted of NOT IN:

SELECT 
  *
FROM   
  table1 ros
  INNER JOIN table2 bal ON ros.value = bal.segment3
  INNER JOIN table3 flx ON flx.value = bal.value AND NVL(ros.ret, 'D') = Nvl(flx.attr16, 'D')
WHERE
  flx.name = 'XXX'
  AND ros.type IN ( 'AL', 'AS', 'PL' )
  AND bal.period = 13
  AND NOT EXISTS ( SELECT 1 FROM table2 WHERE
    code         = bal.code
    AND value    = flx.value
    AND segment3 = ros.value
    AND flag     = bal.flag
    AND period   = 12
    AND year     = bal.year
  )

BTW to make the sub-query fast, create a composite index on table2 that contains all fields you use in the sub-query.

Something among these lines should work with some assumptions. It does an outer join on table2 and eliminates all the records from the result set which have been joined.

select tab.*
from (
  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
) TAB,
table2 BAL1
       AND bal1.code(+) = tab.code
       AND bal1.value(+) = tab.value_flx
       AND bal1.segment3(+) = tab.value_ros
       AND bal1.flag(+) = tab.flag
       AND bal1.period(+) = 12
       AND bal1.year(+) = tab.year
       AND bal1.code is NULL

EDITED to conform the rule that you can outer join to only on one table