[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to write a NOT EXISTS subquery another way.....?

Posted on 2007-08-03
10
Medium Priority
?
1,392 Views
Last Modified: 2013-12-07
Hi all

I have the following query that needs to be written using something other than a NOT EXISTS subquery, please help:


select count(*) from ip a
where not exists
(select * from costedevent b
  where b.account_num = a.account_num
  and b.event_seq = a.bill_SEQ
  and b.event_source = a.mpn)


Thanks


Adi
0
Comment
Question by:freeka
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 1500 total points
ID: 19625153
select count(*) from ip a left outer join costedevent b on a.account_num=b.account_num and a.bill_seq=b.event_seq and a.mpn=b.event_source where b.account_num is null
0
 
LVL 8

Expert Comment

by:YANN0S
ID: 19625168
select SUM (CASE WHEN b.account_num IS NULL THEN 1 ELSE 0 END)
from ip a,
        costedevent b
where b.account_num (+) = a.account_num
  and b.event_seq  (+) = a.bill_SEQ
  and b.event_source  (+) = a.mpn
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19625208
>using something other than a NOT EXISTS subquery
why, btw?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19625253
I agree with Angel, why do you wanna replace the fastest method ?
0
 

Author Comment

by:freeka
ID: 19625281
Hi

I am mapping this in a program called Oracle Warehouse Builder and it is a graphical ETL program that doesn't allow subqueries yet.

I should have also mentioned that I cant use CASE either I don't think.

Hope that helps. I was told it was something to do with outer joins but to be honest the answer from derek is a bit confusing and is still running after 300 seconds compared to 100 for the original.

Thanx


Adi
0
 

Author Comment

by:freeka
ID: 19625352
Sorry Derek, the query stopped with the correct result after 322 seconds which is acceptable considering it isn't going to be as fast as the alternative.

Please can you tell me how this is written using the (+) join syntax as opposed to LEFT OUTER JOIN and then I can close this Q and allocate the points.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19625353
you will need a view with the query... and use that in your front-end tool.

now, 100 seconds looks like you are either having tons of data, or worse, no index on

a.account_num + a.bill_SEQ +  a.mpn ?
0
 

Author Comment

by:freeka
ID: 19625398
Its Oracle Applications so it is TONS and TONS and TONS of data :-)

The view also sounds like a good idea but again is not a viable solution to the problem as Oracle dont like us tinkering with the Applications DB. Instead I need this alternative solution.

Thanks for the assistance.
0
 

Author Comment

by:freeka
ID: 19625457
Anyone? derekkromm?

I need to rewrite this as one that uses alternative syntax (+) instead of LEFT OUTER JOIN.

Here it is again:

select count(*) from ip a left outer join costedevent b on a.account_num=b.account_num and a.bill_seq=b.event_seq and a.mpn=b.event_source where b.account_num is null
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 19625592
try this:

select count(*) from ip a, costedevent b
where a.account_num (+) = b.account_num and
a.bill_seq (+) = b.event_seq and
a.mpn (+) = b.event_source and
b.account_num is null
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question