Help with Join Compatibility

I was hoping someone could help me or put me on the right path to re-writing the join portion of this sql query in ANSI form for compatibility level 90. Im just not sure how to handle the three join statements and if they should go at the top in the FROM statement (dont know if that would mess up the rows produced). The query exists inside a stored proc.

SELECT
S.TYPE,
S.LOCATION_TYPE,
S.LOCATION_ID,
S.PLANNED_ARRIVAL,
S.PROJECTED_ARRIVAL,
S.ACTUAL_ARRIVAL,
S.PLANNED_DEPARTURE,
S.PROJECTED_DEPARTURE,
S.ACTUAL_DEPARTURE
FROM TAB1 S, TAB2 RL, TAB LS
WHERE
S.LOAD_ID = @.V_CURRENTLOADID AND
(RL.REGION_ID = @.REGION_ID AND
RL.ROUTE_DATE = @.ROUTE_DATE AND
RL.ROUTE_ID = @.ROUTE_ID) AND
(S.REGION_ID = RL.REGION_ID AND
S.ROUTE_DATE = RL.ROUTE_DATE AND
S.ROUTE_ID = RL.ROUTE_ID) AND
(S.LOCATION_ID =* LS.LOAD_LOCATION_ID AND

S.LOAD_ID =* LS.LOAD_ID AND
S.LOAD_STOP_ID =* LS.LOAD_STOP_ID)
ORDER BY RL.SEQ_NUM, S.ACTUAL_SEQUENCE_NUM;

Any help would be greatly appreciated

hi,

can u send me the datamodel? based on the datamodel we can form the query

Thanks,

mani

|||Do u want the table descriptions, columns and such? There are no foreign keys on any of the tables and they are independant if that help.|||

The following table,TAB1 S, TAB2 RL, TAB LS what are all the primary key name. what is required results

what is pupose of the table1,table2,table3

i'm in online

thanks,

mani

|||

here are the tables and their definitions. There are no foreign key constraints on these tables. The select statement gets dumped into a cursor. Thanks.

SQL> desc tab1
Name Null? Type
-- -- -

REGION_ID NOT NULL NVARCHAR2(9) PK
ROUTE_DATE NOT NULL DATE PK
ROUTE_ID NOT NULL NVARCHAR2(15) PK
INTERNAL_STOP_ID NOT NULL NUMBER(38) PK

STOP_IX NUMBER(38)
SEQUENCE_NUM NUMBER(38)
ACTUAL_SEQUENCE_NUM NUMBER(38)
TYPE NUMBER(38)
LOCATION_TYPE NVARCHAR2(3)
LOCATION_ID NVARCHAR2(15)

LOAD_ID NUMBER(38)
LOAD_STOP_ID NUMBER(38)

SQL> desc tab2
Name Null? Type
-- -- -

LOAD_ID NOT NULL NUMBER(38) PK

SEQ_NUM NOT NULL NUMBER(38) PK
REGION_ID NOT NULL NVARCHAR2(9) PK
ROUTE_DATE NOT NULL DATE PK
ROUTE_ID NOT NULL NVARCHAR2(9) PK
READ_ONLY NUMBER(38)
DATE_MODIFIED DATE
USER_MODIFIED NVARCHAR2(12)

SQL> desc tab3
Name Null? Type
-- -- --

LOAD_ID NOT NULL NUMBER(38) PK
LOAD_STOP_ID NOT NULL NUMBER(38) PK
SEQ_NUM NUMBER(38)
LOAD_LOCATION_ID NOT NULL NVARCHAR2(15)
PLANNED_SERVICE_TIME NUMBER(38)
ACTUAL_SERVICE_TIME NUMBER(38)
TIME_WINDOW_START DATE
TIME_WINDOW_END DATE

|||

ackermsb wrote:

I was hoping someone could help me or put me on the right path to re-writing the join portion of this sql query in ANSI form for compatibility level 90. Im just not sure how to handle the three join statements and if they should go at the top in the FROM statement (dont know if that would mess up the rows produced). The query exists inside a stored proc.

SELECT
S.TYPE,
S.LOCATION_TYPE,
S.LOCATION_ID,
S.PLANNED_ARRIVAL,
S.PROJECTED_ARRIVAL,
S.ACTUAL_ARRIVAL,
S.PLANNED_DEPARTURE,
S.PROJECTED_DEPARTURE,
S.ACTUAL_DEPARTURE
FROM TAB1 S, TAB2 RL, TAB LS
WHERE
S.LOAD_ID = @.V_CURRENTLOADID AND
(RL.REGION_ID = @.REGION_ID AND
RL.ROUTE_DATE = @.ROUTE_DATE AND
RL.ROUTE_ID = @.ROUTE_ID) AND
(S.REGION_ID = RL.REGION_ID AND
S.ROUTE_DATE = RL.ROUTE_DATE AND
S.ROUTE_ID = RL.ROUTE_ID) AND
(S.LOCATION_ID =* LS.LOAD_LOCATION_ID AND

S.LOAD_ID =* LS.LOAD_ID AND
S.LOAD_STOP_ID =* LS.LOAD_STOP_ID)
ORDER BY RL.SEQ_NUM, S.ACTUAL_SEQUENCE_NUM;

Any help would be greatly appreciated

hi, hope this helps..

SELECT A.TYPE,
A.LOCATION_TYPE,
A.LOCATION_ID,
A.PLANNED_ARRIVAL,
A.PROJECTED_ARRIVAL,
A.ACTUAL_ARRIVAL,
A.PLANNED_DEPARTURE,
A.PROJECTED_DEPARTURE,
A.ACTUAL_DEPARTURE
FROM TAB1 A
INNER JOIN TAB2 B
ON A.REGION_ID = B.REGION_ID
AND A.ROUTE_DATE = B.ROUTE_DATE
AND A.ROUTE_ID = B.ROUTE_ID
INNER JOIN TAB C
ON A.LOCATION_ID = C.LOAD_LOCATION_ID
AND A.LOAD_ID = C.LOAD_ID
AND A.LOAD_STOP_ID = C.LOAD_STOP_ID
WHERE A.LOAD_ID = @.V_CURRENTLOADID
AND B.REGION_ID = @.REGION_ID
AND B.ROUTE_DATE = @.ROUTE_DATE
AND B.ROUTE_ID = @.ROUTE_ID
ORDER BY B.SEQ_NUM, A.ACTUAL_SEQUENCE_NUM|||

SELECT
A.TYPE,
A.LOCATION_TYPE,
A.LOCATION_ID,
A.PLANNED_ARRIVAL,
A.PROJECTED_ARRIVAL,
A.ACTUAL_ARRIVAL,
A.PLANNED_DEPARTURE,
A.PROJECTED_DEPARTURE,
A.ACTUAL_DEPARTURE

From Table1 A,Table2 B,Table3 C

where B.LOAD_ID = C.LOAD_ID AND
A.REGION_ID = B.REGION_ID AND
A.LOAD_ID = @.V_CURRENTLOADID AND
B.REGION_ID = @.REGION_ID AND
B.ROUTE_DATE = @.ROUTE_DATE AND
B.ROUTE_ID = @.ROUTE_ID

|||

Rom-Rom,

I believe the way this is written is accurate however would this A =* B be a right outter join and not an inner join hence

FROM TAB1 A

INNER JOIN TAB2 B

ON A.REGION_ID = B.REGION_ID

AND A.ROUTE_DATE = B.ROUTE_DATE

AND A.ROUTE_ID = B.ROUTE_ID

RIGHT OUTER JOIN TAB C

ON A.LOCATION_ID = C.LOAD_LOCATION_ID

AND A.LOAD_ID = C.LOAD_ID

AND A.LOAD_STOP_ID = C.LOAD_STOP_ID

Let me know what you think

|||hi ackermsb,

it really depends on how the data should be presented. i made it inner joins because i thought you want existing data for related tables to be returned (that is what the equation of related fields between tables from your original query suggested).

you may do that right outer join if you want "all" records of table "tab" to exist on the result regardless if it exists on the joins of table "tab1" and "tab2", inexistent records for the latter tables will be regarded as null fields.

it's up to you, i may not know what requirements you need. hope i've been of help.

good day.