Left-right outer join problem

[Eshwar Prasad]

I have a query written in procedure as below

select user,pass from 
A a,
B b,
C c
D d
where 
a.user=b.user;
--------------

some lines of joins...

------------------
a.id=b.id and
--------------

some lines of joins...

------------------ 
and c.id=d.id(+) and /*Here if admin is available i should join as c.id(+)=d.id if it is a tester i have to join as c.id=d.id(+)*/

a.superuser="admin"
/*a.superuser="tester"*/

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]

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]

so writing sql query with small change in if else block of proc would be most appropriate. Is that correct.

[Fatih Keles]

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]

Thanks i will follow your suggestion