Geek Answers Handbook

Convert legacy external connection to ANSI

I ran into the following legacy PL / SQL and found that external joins with scalar constants were somewhat confusing. First of all, someone can confirm that my attempt to convert this to ANSI is correct.

LEGACY CODE :

cursor c1item (c1item_iel_id number) is select `<columns>` from iel_item iit, iel_item_property iip where iit.iit_change_type = 'I' and iip.iip_change_type (+) = 'I' and iip.it_id (+) = iit.it_id and iit.iel_id = c1item_iel_id and iip.iel_id (+) = c1item_iel_id;

ANSI CODE

 cursor c1item (c1item_iel_id number) is select `<columns>` from iel_item iit left outer join iel_item_property iip on iip.it_id = iit.it_id and iit.iit_change_type = 'I' and iip.iip_change_type = 'I' and iit.iel_id = c1item_iel_id and iip.iel_id = c1item_iel_id;

If this is correct, I see no reason to use an external connection. Of course, if the primary key it_id in the iit table does not have a corresponding foreign key in the iip table, while iip.iit_change_type strong> and iip.iel_id will be NULL, in which case they will be filtered by AND clauses. So why not just use the inner join? Am I missing something? or is the original code error?

0
source share
3 answers

No, this is not true - only those marked with a "(+)" must be in LEFT JOIN, the rest for the WHERE clause:

  SELECT `<columns>` FROM iel_item iit LEFT JOIN iel_item_property iip ON iip.it_id = iit.it_id AND iip.iip_change_type = 'I' AND iip.iel_id = c1item_iel_id WHERE iit.iit_change_type = 'I' AND iit.iel_id = c1item_iel_id

Placement Issues with OUTER JOINs — The criteria in the ON clause applies before the JOIN, while the criteria in WHERE applies after the JOIN. This can greatly affect the returned result set, depending on the data and settings. The placement does not matter for INNER JOINS - in the WHERE or ON clause, the result set will be the same.

+6
source

No, this is wrong. There were only 3 external join predicates in the original query, all 5 in your new query.

Must be:

 cursor c1item (c1item_iel_id number) is select `<columns>` from iel_item iit left outer join iel_item_property iip on iip.it_id = iit.it_id and iip.iip_change_type = 'I' and iip.iel_id = c1item_iel_id where and iit.iel_id = c1item_iel_id and iit.iit_change_type = 'I' ;
+3
source

It can also be rewritten as follows:

 SELECT columns1 FROM iel_item iit RIGHT OUTER JOIN iel_item_property iip ON iip.iip_change_type = 'I' AND iip.it_id = iit.it_id AND iip.iel_id = c1item_iel_id WHERE iit.iit_change_type = 'I' AND iit.iel_id = c1item_iel_id

Next time, if you need to rewrite Oracle proprietary connections to ANSI compatible connections, here is a tool that can help you do this automatically with less chance of error.

0
source

More articles:


All Articles