Home » SQL & PL/SQL » SQL & PL/SQL » EXISTS/NOT EXIST/ NOT IN/MINUS
EXISTS/NOT EXIST/ NOT IN/MINUS [message #238618] Fri, 18 May 2007 13:36 Go to next message
dimples0109
Messages: 6
Registered: March 2007
Junior Member
hello experts

I have been working on what seems like a simple query for about five days now. And I just need some advice.

I have created 2 temporary tables
active_accounts - this lists all active accounts
active_BI_accounts - this lists active accounts with the most current Billing addresses

I need one flat file that will combine them without duplicate entries. The index field I am working with is call cust_code. I was told that I could say the following but it runs for hours(300,000 rows).

select * from active_accounts where cust_code not in (select cust_code from active_BI_accounts)


Then I tried not exists, exists and minus. all fail.
I have attached my 12th version of the code.

I hope I formatted the post correctly because I really need help. I have googled, looked at other code and ran every variation of the above I could think of. appreciate your help
Re: EXISTS/NOT EXIST/ NOT IN/MINUS [message #238619 is a reply to message #238618] Fri, 18 May 2007 13:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Then I tried not exists, exists and minus. all fail.
I'll stipulate that the statement above is true, but it adds NOTHING to an outside observer.
SQL can "fail" for any 1 of thousands or reasons.
Are there indexes on CUST_CODE for both tables & are STATISTICS current for both tables?
Post EXPLAIN_PLAN for the query that "works".
What problem are you really trying to solve & how will I know when this problem has been solved?
Re: EXISTS/NOT EXIST/ NOT IN/MINUS [message #238635 is a reply to message #238618] Fri, 18 May 2007 15:52 Go to previous message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Just a shot in the dark, but perhaps the following approach may work better than what you've tried to-date.

select ucbcust_last_name, ucbprem_street_number -- ...
from
(
  select
    ucbcust_last_name, ucbprem_street_number -- ...
    , row_number() over( partition by cust_code order by priority ) as score
  from
  ( select 1 as priority, cust_code, ucbcust_last_name, ucbprem_street_number -- ...
    from active_bi_accounts
    union all
    select 2 as priority, cust_code, ucbcust_last_name, ucbprem_street_number -- ...
    from active_prem_accounts
  )
)
where score = 1 ;


Good luck.

--
Joe Fuda
http://www.sqlsnippets.com/
Previous Topic: how can we use date in for loop
Next Topic: How to get First 100 odd Numbers using Rownum
Goto Forum:
  


Current Time: Sat Dec 10 00:57:01 CST 2016

Total time taken to generate the page: 0.04821 seconds