I am trying to convert an Informix query to Oracle:
The Informix query looks like this:
SELECT
r.aa, n.bb, nd.cc,u.id, ud.dd, g.attr
FROM
tab1 u, tab2 ud,
OUTER (tab3 a, tab4 n, tab5 nd, tab6 r, OUTER (tab7 g, tab8 atr))
WHERE
r.xx = n.xx AND
n.nas = a.nas AND
a.user = u.user AND
a.ac = g.ac AND
n.nas1 = nd.nas1 AND
u.user1 = ud.user1 AND
atr.sso = g.sso AND
UPPER(atr.name) = 'NAME' AND
u.id = 102
The Oracle query looks like this:
SELECT
r.aa, n.bb, nd.cc,u.id, ud.dd, g.attr
FROM
tab1 u
INNER JOIN tab2 ud ON
u.user1 = ud.user1 AND
u.id = 102
LEFT OUTER JOIN tab3 a ON a.user = u.user
LEFT OUTER JOIN tab4 n ON n.nas = a.nas
LEFT OUTER JOIN tab5 nd ON n.nas1 = nd.nas1
LEFT OUTER JOIN tab6 r ON r.xx = n.xx
I am not sure how to join the other two tables.
Can any one help me?
I believe the query should look something like this:
SELECT r.aa, n.bb, nd.cc, u.id, ud.dd, g.attr
FROM tab1 AS u
INNER JOIN tab2 AS v ON u.user1 = v.user1 AND u.id = 102
LEFT OUTER JOIN tab3 AS a ON a.user = u.user
LEFT OUTER JOIN tab4 AS n ON n.nas = a.nas
LEFT OUTER JOIN tab5 AS d ON n.nas1 = d.nas1
LEFT OUTER JOIN tab6 AS r ON r.xx = n.xx
LEFT OUTER JOIN (SELECT g.attr, g.ac
FROM tab7 AS x
JOIN tab8 AS atr ON x.sso = atr.sso
WHERE UPPER(atr.name) = 'NAME'
) AS g ON a.ac = g.ac
I changed the alias 'nd' to just 'd' and 'ud' to 'v' so that all aliases are single-letters. The nested OUTER(tab7 g, tab8 atr)
in the Informix notation is itself an inner join (as in the sub-select in my version), but that result set is outer joined with a.ac
. This is what the rewrite says.
I used a WHERE clause in the sub-query; the WHERE condition could be left in the ON clause if you preferred. The chances are the optimizer will handle both correctly and equivalently. Similarly, the AND u.id = 102
in the inner join could be placed into a WHERE clause. Again, the optimizer would probably push the filter condition down for better performance.
Note that the UPPER function in the sub-query likely requires a table-scan - unless you have a functional index on UPPER(atr.name)
.
Revisiting this, the transliteration of the initial part of the query is not accurate.
The original query included the FROM clause:
FROM tab1 u, tab2 ud, OUTER(tab3 a, tab4 n, tab5 nd, tab6 r, OUTER(tab7 g, tab8 atr))
The tables tab3
, tab4
, tab5
and tab6
are inner-joined to each other, and the result is outer-joined to tab1
and tab2
. Similarly, tab8
is inner-joined to tab7
, but the result of that is outer-joined to the inner-join of tables 3-6. The original answer I gave (based on the outline answer in the question) would be represented in the old Informix notation using:
FROM tab1 u, tab2 ud,
OUTER(tab3 a, OUTER(tab4 n, OUTER(tab5 nd, OUTER(tab6 r, OUTER(tab7 g, tab8 atr)))))
Thus, it would be more accurate to transcribe the original query as:
SELECT r.aa, n.bb, nd.cc, u.id, ud.dd, g.attr
FROM tab1 AS u
JOIN tab2 AS v ON u.user1 = v.user1 AND u.id = 102
LEFT OUTER JOIN
(SELECT *
FROM tab3 AS a ON a.user = u.user
JOIN tab4 AS n ON n.nas = a.nas
JOIN tab5 AS d ON n.nas1 = d.nas1
JOIN tab6 AS r ON r.xx = n.xx
LEFT OUTER JOIN
(SELECT g.attr, g.ac
FROM tab7 AS x
JOIN tab8 AS atr ON x.sso = atr.sso
WHERE UPPER(atr.name) = 'NAME'
) AS g ON a.ac = g.ac
) AS loj
The residual problem would be ensuring that the correct aliases are in use for the columns from the complex loj
sub-query. Note that in the absence of LEFT, RIGHT or FULL, a JOIN is assumed to be an INNER join; also, if you specify LEFT, RIGHT or FULL, the OUTER is optional.
One other detail to note: the behaviour of the old-style Informix OUTER join under filter conditions is not the same as the behaviour of Standard SQL OUTER joins. This seldom makes a difference, but it could occasionally be important. On the whole, the behaviour of Standard SQL OUTER joins is more usually what you want, but you if you run regression tests and find that there's a difference in the answers, the explanation might be that the old-style Informix OUTER join does things differently from the new-style Standard SQL OUTER joins.