Home » SQL & PL/SQL » SQL & PL/SQL » Logic of rewriting a Query!!! (SQL Performance Issue)
Logic of rewriting a Query!!! [message #289204] Thu, 20 December 2007 03:29 Go to next message
maggy_ashee
Messages: 70
Registered: May 2007
Member
Hi all,

The below query is just a part of my Original Query,I am facing Performance issue in the Query.This query inturn will pop up a Java page and display the results.

Issue :
1. I need a logic wherein if the join1 returns NULL values then it should ignore the next join join2,if it returns a value then
it should go for the next join join2.(Hope this clear)

.
.
join1 and h.acc_no = nvl (ak.value,h.acc_no)
join2 and ak.att_code = 'CUST_N0'
join3 and ak.user = bk.user_id
.
.
Can someone help me in rewriting this Query

Appreciate your response!!!

Re: Logic of rewriting a Query!!! [message #289206 is a reply to message #289204] Thu, 20 December 2007 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rows are only return if ALL joins are fit.
There is nothing to do, just write them.

Regards
Michel
Re: Logic of rewriting a Query!!! [message #289217 is a reply to message #289204] Thu, 20 December 2007 05:22 Go to previous messageGo to next message
maggy_ashee
Messages: 70
Registered: May 2007
Member
Hi ,

I tried to include a sub query for this and even tried to write a package ,but none is working.
Re: Logic of rewriting a Query!!! [message #289218 is a reply to message #289217] Thu, 20 December 2007 05:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I will give you a very tiny example. I think this is what you are after
(a.col1 = b.col1
and b.col1 = c.col1
and c.col1 = d.col1)
or
(a.col1 = b.col1
and b.col1 = c.col1)
or
(a.col1 = b.col1)

The way it equates is get me the records which matches any of those conditions. Try to build your logic from there...

HTH

Regards

Raj
Re: Logic of rewriting a Query!!! [message #289248 is a reply to message #289218] Thu, 20 December 2007 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use outer join on optional join.

Regards
Michel
Re: Logic of rewriting a Query!!! [message #289346 is a reply to message #289248] Thu, 20 December 2007 19:49 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
FROM h
LEFT JOIN (
    SELECT ak.value
    FROM   ak
    JOIN   bk ON bk.user_id = ak.user
    WHERE  ak.att_code = 'CUST_N0' 
) x ON x.value = h.acc_no


Warning: this type of query may produce performance problems if you are only selecting a few rows from a large table.

Ross Leishman
Previous Topic: Trigger for Changes in Database
Next Topic: Display duplicate record (merged)
Goto Forum:
  


Current Time: Fri Dec 09 23:18:53 CST 2016

Total time taken to generate the page: 0.28600 seconds