• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Left-right outer join problem

 
Skanda Raman
Ranch Hand
Posts: 205
Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Skanda Raman
Ranch Hand
Posts: 205
Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
so writing sql query with small change in if else block of proc would be most appropriate. Is that correct.
 
Fatih Keles
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Skanda Raman
Ranch Hand
Posts: 205
Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks i will follow your suggestion
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic