it-rex
asked on
from ansi sql to oracle sql
we have inherted some code from another depratment,and we need to convert it to oracle from ansi we have done this alot before ,but this one is confusing because we have too many (( and )) and we do not really know how they affect the from and where clause,
please advise.
thanks
FROM ( ( ( ( subj
LEFT JOIN
( care_inc
LEFT JOIN
( v_mat
LEFT JOIN
MATERIAL
ON v_mat.MATERIAL_ID =
MATERIAL.MATERIAL_ID)
ON (care_inc.one_ID =
v_mat.one_ID)
AND care_inc.care_seq =
v_mat.care_seq)
ON (subj.care_seq =
care_inc.care_seq)
AND (subj.one_ID =
care_inc.one_ID))
LEFT JOIN
one
ON subj.one_ID = one.one_ID)
LEFT JOIN
BASIC_MODULE_one_INFO
ON (subj.MODULE_ID =
BASIC_MODULE_one_INFO.MODU
AND (subj.care_seq =
BASIC_MODULE_one_INFO.care
AND (subj.one_ID =
BASIC_MODULE_one_INFO.one_
LEFT JOIN
one_FACILITY_INFO
ON (care_inc.FACILITY_ID =
one_FACILITY_INFO.FACILITY
AND (care_inc.one_ID =
one_FACILITY_INFO.one_ID))
LEFT JOIN
PERSON
ON subj.worker_TEXT = PERSON.FULL_NAME
please advise.
thanks
FROM ( ( ( ( subj
LEFT JOIN
( care_inc
LEFT JOIN
( v_mat
LEFT JOIN
MATERIAL
ON v_mat.MATERIAL_ID =
MATERIAL.MATERIAL_ID)
ON (care_inc.one_ID =
v_mat.one_ID)
AND care_inc.care_seq =
v_mat.care_seq)
ON (subj.care_seq =
care_inc.care_seq)
AND (subj.one_ID =
care_inc.one_ID))
LEFT JOIN
one
ON subj.one_ID = one.one_ID)
LEFT JOIN
BASIC_MODULE_one_INFO
ON (subj.MODULE_ID =
BASIC_MODULE_one_INFO.MODU
AND (subj.care_seq =
BASIC_MODULE_one_INFO.care
AND (subj.one_ID =
BASIC_MODULE_one_INFO.one_
LEFT JOIN
one_FACILITY_INFO
ON (care_inc.FACILITY_ID =
one_FACILITY_INFO.FACILITY
AND (care_inc.one_ID =
one_FACILITY_INFO.one_ID))
LEFT JOIN
PERSON
ON subj.worker_TEXT = PERSON.FULL_NAME
ASKER
management wants to convert all sql to oracle sql as they more readable and understandable..
not my choice.
not my choice.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sdstuber;
I thought that we only outer using (+) whenever we see "on";
but if there is "and" we show the join as is without (+),
please advise
I thought that we only outer using (+) whenever we see "on";
but if there is "and" we show the join as is without (+),
please advise
no,
if you do this...
AND x.col1 = y.col1(+)
and x.col2 = y.col2
that's the same thing as...
ON x.col1 = y.col
WHERE x.col2 = y.col2
which is the same thing as...
AND x.col1 = y.col1
AND c.col2=y.col2
which is an inner join
in order to do outer joins you must put the (+) on ALL references to the outer joined table in the where clause
if you do this...
AND x.col1 = y.col1(+)
and x.col2 = y.col2
that's the same thing as...
ON x.col1 = y.col
WHERE x.col2 = y.col2
which is the same thing as...
AND x.col1 = y.col1
AND c.col2=y.col2
which is an inner join
in order to do outer joins you must put the (+) on ALL references to the outer joined table in the where clause
ASKER
your works,you are the man.
ASKER
great
and the extra () aren't doing anythig, you can remove them