In this example, yes, looks like you need a union. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 12:44 PM
> Perhaps this small example can make it clear? > I have two tables, orders and order_to_delete. > I want to find orders to KEEP - i.e. the order_id is not in table order_to_delete, or it's in table order_to_delete with a status 'N'. > > If I'm using outer joins, I think I need a a union, nicht wahr? > > drop table order_to_delete ; > drop table orders ; > create table orders (order_id number not null, order_date date) ; > create table order_to_delete (order_id number not null, delete_flag varchar2 (1)) ; > insert into orders (order_id, order_date) > values (1, sysdate - 1) ; > insert into orders (order_id, order_date) > values (2, sysdate - 2) ; > insert into orders (order_id, order_date) > values (3, sysdate - 3) ; > insert into order_to_delete (order_id, delete_flag) > values (1, 'Y') ; > insert into order_to_delete (order_id, delete_flag) > values (2, 'N') ; > commit ; > select a.order_id, a.order_date > from orders a, order_to_delete b > where a.order_id = b.order_id > and b.delete_flag = 'N' > union > select a.order_id, a.order_date > from orders a, order_to_delete b > where a.order_id = b.order_id (+) > and b.order_id is null ; > > This query will return order ids 2 and 3, the ones I want to KEEP. The query returns the same value in Oracle 8.1 and Oracle 9.2 > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > > Binley Lim > > > > On a general note, this older outer join syntax to simulate a not-in > > requires ALL the "b" columns to include the (+) sign, including the > > "nvl...", except the "b.cusip is null", which is the not-in > > itself. If you > > miss one, the logic is completely changed. > > > > 9i's new syntax does outer-joins by doing the outer on the > > tables, rather > > than the columns, so you can no longer do this outer "simulation"! > > > > In this case, you can achieve the same thing with a not-in, > > rather than (+), > > in the SQL syntax (assuming you see a HASH AJ in the plan somewhere). > > > > ----- Original Message ----- > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Thursday, September 18, 2003 9:04 AM > > > > > > > [EMAIL PROTECTED] wrote: > > > > > > > > create table ani_prx_faster parallel (degree 5) nologging > > > > as > > > > select b.* > > > > from bo_owner_master.ani_prx b, > > > > bo_owner_stage.ani_prx a > > > > where a.cusip = b.cusip (+) > > > > and a.fund_no = b.fund_no (+) > > > > and a.add_cymd = b.add_cymd (+) > > > > and nvl(b.ba_reccode, 'X') != 'V' > > > > and b.cusip is null > > > > > > > > This query I got from here I got only 638k, and I have > > 27m records in my > > file and there are 17m deletes. so 12m records left. Also all > > the records > > are null???? > > > > > > > > what do i need to change? > > > > > > > > > > > Ooops, I have permuted a's and b's in the where clause ... > > > > > > Otherwise not to sure about the nvl(). Wait a minute ... > > we are getting > > > rows for which there is no matching (cusip, fund_no, > > add_cymd) in the > > > staging table. Perhaps that the 'cusip is null' condition > > should go ... > > > the nvl() should be enough. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jacques Kilchoer > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
