Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and the fly likes Left-right outer join problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Left-right outer join problem" Watch "Left-right outer join problem" New topic
Author

Left-right outer join problem

Eshwar Prasad
Ranch Hand

Joined: Mar 21, 2008
Posts: 202
I have a query written in procedure as below



Here if admin is a condition i should join as c.id(+)=d.id else if it is a tester i have to join as c.id=d.id(+)

I am using PL/SQL procedure. This is a complex query with muliple joins.

I can duplicate the query and use this in procedure by putting query in IF-ELSE blocks. But due change in one line duplication is not correct.

Please suggest how to resolve.
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
Hi Eshwar,

Do you need both "admin" and "tester" results in your cursor? If the answer is "No" than do not try write a fancy tricky sql query because:
1. it will mass up index usage if you have many joins and costly execution plan.
2. maintenance is difficult, each time you need to add something you will have to decode your fancy statements, understand it and re-implement it according to changing requirement.

Regards,

Fatih.
Eshwar Prasad
Ranch Hand

Joined: Mar 21, 2008
Posts: 202
so writing sql query with small change in if else block of proc would be most appropriate. Is that correct.
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
I believe writing very complex sql statements that will handle any situation is not a good approach. I would prefer a bunch of simple cursors rather than one complex cursor.

Regards,

Fatih.
Eshwar Prasad
Ranch Hand

Joined: Mar 21, 2008
Posts: 202
Thanks i will follow your suggestion
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Left-right outer join problem
 
Similar Threads
Need to "simulate" multiple full outer joins on db2
which join to use?
accessing parent of child
Combining Multiple Inner Joins with a Left Join
Overriding rule for generics