I've been porting oracle chooses, and I've been running across lots of queries like so:
SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id;
SELECT last_name, d.department_id FROM employees e, departments d WHERE e.department_id = d.department_id(+);
What are the guides/lessons for transforming all the variants from the (+) syntax? What's that syntax even known as (in order to scour google)?
Better still.. It is possible to tool/script that is going to do this conversion for me personally (Preferred Free)? An optimizer of some kind? I've around 500 of those queries to port..
Just when was this standard eliminated? Any info is appreciated.
(+) is Oracle specific pre-ANSI-92 OUTER JOIN syntax, because ANSI-89 syntax does not provide syntax for
OUTER JOIN support.
LEFT is dependent upon which table &lifier column reference the notation is mounted on. If it's specified alongside a column connected using the first table within the
FROM clause - it is a
RIGHT join. Otherwise, it is a
LEFT join. This a great reference for anybody requiring to understand the main difference between JOINs.
First query re-written using ANSI-92 syntax:
SELECT e.lastname, d.department_name FROM EMPLOYEES e RIGHT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid
Second query re-written using ANSI-92 syntax:
SELECT e.lastname, d.department_name FROM EMPLOYEES e LEFT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid
Google "Oracle join syntax". the
(+) can be used for various flavours of outer joins. I believe the first you demonstrated is really a Left Outer Join, and also the second the first is the right Outer Join. I've not seen this notation for a long time, and so i can be somewhat off, but hopefully, this provides you sufficient info hitting Google and obtain the best answer.
Which means you wants something to get it done for you personally? I've heard that SwisSQL can perform something similar to this, but when the majority of the queries are pretty straight forward enough you are able to most likely write just a little script that will it for you personally. OMG Ponies answer nicely shows the pattern for transforming from old to new syntax.
This could get quite complicated because the WHERE clause in even simple situations for example
WHERE e.id = d.manager_id(+) OR e.id = d.contact_id(+)
will translate to UNION or subselect query.
If you want python you may have a look at sqlparse, it appears promising and you can find it to complete the thing you need and several reformatting from the SQL code. It might easily work on the origin. You will need to tell it how to proceed however it does relieve you of writing the boring parsing part.
For Oracle 10g
You'll find another versions of oracle manuals online in case your version differs, although the join syntax is most likely not different.
I appear to consider this syntax disappearing within the transition from Oracle 8i to 9i -- I believe we'd a toad wordpress plugin that wound up transforming everything for all of us so we did not need to spend your time dealing with every query