- A way to avoid ORA-01417
- Advantages of ANSI syntax
- Example SQL – comparing Oracle vs. ANSI syntax
You might notice that I mainly use ANSI SQL syntax on this site, instead of Oracle syntax.
I used to write SQL in Oracle using Oracle syntax – such as:
SELECT fu.user_name FROM applsys.fnd_user fu , ar.hz_parties hp , hr.per_all_people_f papf WHERE fu.person_party_id = hp.party_id(+) AND papf.party_id = hp.party_id(+) AND hp.party_type = 'PERSON';
However, if I wrote the SQL above and tried to run it, I’d get this error when doing so because I am trying to outer join the
hz_parties table to the
fnd_user at the same time:
ORA-01417: a table may be outer joined to at most one other table
A way to avoid ORA-01417
A colleague said he uses ANSI syntax, where you explicitly write the join names in your SQL.
SELECT hp.party_name , hp.party_number , papf.employee_number , fu.user_name FROM ar.hz_parties hp LEFT JOIN hr.per_all_people_f papf ON hp.party_id = papf.party_id LEFT JOIN applsys.fnd_user fu ON fu.person_party_id = hp.party_id WHERE hp.party_type = 'PERSON' AND hp.creation_date > '01-OCT-2015';
Advantages of ANSI syntax
There are various advantages to using this approach:
- You can outer join a table to more than one other table – in the example above, the hz_parties table is outer joined to the per_all_people_f and fnd_user, and there is no error.
- You can introduce a table name and join all in the same line, so that it looks a lot tidier – if you want to comment out a table, just comment out the line with the table name and join on it, instead of in two places when using Oracle syntax. Also all of the info is in one place – e.g. the table name and join details.
- The “WHERE” part of your SQL can be used to restrict your query to include specific records, instead of needing to include many lines of joins, so the SQL is more readable.
- If you get using to writing SQL using ANSI syntax, you can use that in other databases such as MySQL, Access and SQL Server.
This is a useful post on Stackoverflow which goes into more detail: Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
The SQLs below compare two statements, one written with Oracle syntax, the other with ANSI. There is no difference in the cost when viewing the Explain Plan for the 2 statements.
It’s probably a matter of preference, and doesn’t make a lot of difference really, as the statements below look very similar, but I think I prefer the ANSI format as I think it looks tidier and I find it easier to understand.
Example SQL – comparing Oracle vs. ANSI syntax
-- requisition headers and lines and line_locations_all, joined through to PO Headers and Lines -- outer joined to po tables so can get REQ details which are not linked to PO SELECT prha.segment1 requisition, prha.requisition_header_id , prha.creation_date , prha.authorization_status , prla.line_num , prla.unit_price , prla.quantity , pha.segment1 po , pha.po_header_id , pla.line_num po_line_number , pla.po_line_id , plla.line_location_id FROM po.po_requisition_headers_all prha , po.po_requisition_lines_all prla , po.po_line_locations_all plla , po.po_lines_all pla , po.po_headers_all pha WHERE prha.requisition_header_id = prla.requisition_header_id AND prla.line_location_id = plla.line_location_id(+) AND plla.po_line_id = pla.po_line_id(+) AND plla.po_header_id = pha.po_header_id(+) AND prha.creation_date > '23-NOV-2015' ORDER BY prha.creation_date DESC , prha.segment1 , prla.line_num; -- requisition headers and lines and line_locations_all, joined through to PO Headers and Lines -- left joined to po tables so can get REQ details which are not linked to PO SELECT prha.segment1 requisition, prha.requisition_header_id , prha.creation_date , prha.authorization_status , prla.line_num , prla.unit_price , prla.quantity , pha.segment1 po , pha.po_header_id , pla.line_num po_line_number , pla.po_line_id , plla.line_location_id FROM po.po_requisition_headers_all prha JOIN po.po_requisition_lines_all prla ON prha.requisition_header_id = prla.requisition_header_id LEFT JOIN po.po_line_locations_all plla ON prla.line_location_id = plla.line_location_id LEFT JOIN po.po_lines_all pla ON plla.po_line_id = pla.po_line_id LEFT JOIN po.po_headers_all pha ON plla.po_header_id = pha.po_header_id WHERE prha.creation_date > '23-NOV-2015' ORDER BY prha.creation_date DESC , prha.segment1 , prla.line_num;