8 Join Table... Access to Oracle... Think your good? Try a..

OK, so I was given this very large query that is trying to create a
very large grid table to hold all the necessary information. I really
dont like it and suggested breaking it up, but the boss is the boss,
and they want to keep it the same so implementation doesn't change :S.
Below I will show the FROM section of the Access query, and the
attempt to match that query in the Oracle WHERE clause by another
developer.

The problem is that currently they are getting 3000 extra records, so
they want me to find the problem.

Access (I tried to structure it nicely):

FROM

(PERSON LEFT JOIN RD_GENDER ON PERSON.GENDER_ID = RD_GENDER.VALUE_ID)
RIGHT JOIN
(((((
[EMHealth Subject Type] RIGHT JOIN
(b_collection_pt RIGHT JOIN b_Data_From_Specimn ON
b_collection_pt.SPECIMN_ID = b_Data_From_Specimn.SPECIMN_ID)
ON
[EMHealth Subject Type].HSBJ_ID = b_Data_From_Specimn.HSBJ_ID)

LEFT JOIN

b_get_prod_data_Crosstab ON b_Data_From_Specimn.SPECIMN_ID =
b_get_prod_data_Crosstab.SPECIMN_ID)

LEFT JOIN

HMN_HSBJ ON [EMHealth Subject Type].HSBJ_ID = HMN_HSBJ.HSBJ_ID)

LEFT JOIN

ANML_HSBJ ON [EMHealth Subject Type].HSBJ_ID = ANML_HSBJ.HSBJ_ID)

LEFT JOIN

RD_GENDER AS RD_GENDER_1 ON ANML_HSBJ.GENDER_ID = RD_GENDER_1.VALUE_ID)


ON
PERSON.PERSON_ID = HMN_HSBJ.PERSON_ID.

(I know its ugly......)


Oracle:

WHERE

WHERE
B_DATA_FROM_SPECIMNS.HSBJ_ID = EMHEALTH_SUBJECT_TYPE.HSBJ_ID(+)
AND
B_DATA_FROM_SPECIMNS.SPECIMN_ID = B_COLLECTION_PT.SPECIMN_ID(+)
AND
B_DATA_FROM_SPECIMNS.SPECIMN_ID =
B_GET_PROD_DATA_CROSSTAB.SPECIMN_ID(+)
AND
PERSON.GENDER_ID = RD_GENDER.VALUE_ID(+)
AND
ANML_HSBJ.GENDER_ID=RD_GENDER_1.VALUE_ID(+)
AND
HMN_HSBJ.PERSON_ID=PERSON.PERSON_ID(+)
AND
HMN_HSBJ.HSBJ_ID(+)=EMHEALTH_SUBJECT_TYPE.HSBJ_ID
AND
ANML_HSBJ.HSBJ_ID(+)=EMHEALTH_SUBJECT_TYPE.HSBJ_ID


Now some of the Right and Left Joins are mixed up between the Oracle
and Access versions, but I think they did this because Oracle doesn't
allow two outer joins on the same table in one query.

As I said before, I was thinking unions might be needed. But if anyone
has any ideas, or in some miracluous way, an answer.... Post er up!

Thanks for any help you could give,

Mitch Gillespie

mgillesp.TakeThisOut@uoguelph.ca wrote:

> Oracle:
>
> WHERE
>
> WHERE
> B_DATA_FROM_SPECIMNS.HSBJ_ID = EMHEALTH_SUBJECT_TYPE.HSBJ_ID(+)
> AND
> B_DATA_FROM_SPECIMNS.SPECIMN_ID = B_COLLECTION_PT.SPECIMN_ID(+)
> AND
> B_DATA_FROM_SPECIMNS.SPECIMN_ID =
> B_GET_PROD_DATA_CROSSTAB.SPECIMN_ID(+)
> AND
> PERSON.GENDER_ID = RD_GENDER.VALUE_ID(+)
> AND
> ANML_HSBJ.GENDER_ID=RD_GENDER_1.VALUE_ID(+)
> AND
> HMN_HSBJ.PERSON_ID=PERSON.PERSON_ID(+)
> AND
> HMN_HSBJ.HSBJ_ID(+)=EMHEALTH_SUBJECT_TYPE.HSBJ_ID
> AND
> ANML_HSBJ.HSBJ_ID(+)=EMHEALTH_SUBJECT_TYPE.HSBJ_ID
>
>
> Now some of the Right and Left Joins are mixed up between the Oracle
> and Access versions, but I think they did this because Oracle doesn't
> allow two outer joins on the same table in one query.

Sure it does. You just need to code in-line views and then join them.
--
Daniel A. Morgan
University of Washington
damorgan.TakeThisOut@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org