How can I retrieve data from 6 join table?? URGENT HELP PLEASE!!!!!

[sommererdbeere]

hi,

this is a little complicated and i will try my best to explain it. i have 6 tables in my sql statement, in which i'm having difficulty to retreive some data from database.

database: oracle 8i

here are the 6 tables:

1. SO_OP soo
2. SO so
3. Shop_Mat_rev smrs
4. MS ms
5. Pur_req pr
6. Rout_Op ros

her is my sql statment:

select
soo.wc soo_wc,
so.so_ordno so_ordno,
soo.op_ordno op_ordno,
pr.pr_ordno pr_ordno,
soo.soo_partno soo_partno,
smrs.smrs_partno smrs_partno,
ms.qtyassem ms_qtyassem
ros.ros_partno ros_partno

from
SO_OP SOO, SO so,
Shop_Mat_rev SMRS, MS ms,
Pur_req pr, Rout_Op ros

where
smrs.smrs_ordno = soo.soo_ordno and
pr.pr_ordno = so.soo_ordno and
smrs.smrs_ordno = so.so_ordno and
ms.partno = so.so_partno and
ros.ros_partno = so.so_partno and
so.state != 'Closed'

Problem:
some orders doesn't happen to be in some of the tables, for example. for ordno = '123'. it has it on
1. SO_OP soo
2. SO so
3. Shop_Mat_rev smrs
6. Rout_Op ros

however, it doesn't have it on
4. MS ms
5. Pur_req pr

where as some of them doesn't have it on Pur_req and SO_OP table or vice versa.

However, all of the orders have it on SO table.

i try to use outer join, but it doesn't work.

Can any of experts please tell me how would i appoarch??
please give me any solution that you will think it is capable in doing it.. This is very urgent, which i haven't sleep for 2 days to figure this out.

million million million trillion thanks for those who help..

thank u

 

[aryeh1010]

If the order number is always in the SO table, but not neccessarily in any of the other tables, you do an outer join from SO to each of the other tables.

If the order number always appears in some of the tables, then you don't need to use an outer join between the SO table and them.

Without knowing how large the tables are and how many records you expect to see as the result of the select, it's hard to give more advice.

Sometimes it's easier to split a query like this into an insert of the baseline query data into a holding table, which can be a global temporary table, then do updates of the fields from the other tables.

At the end do one clean select from the holding table.

I've solved lots of problems like these for developers by splitting things up, when SQL tuning could go no further.

Aryeh Keefe

 

[sommererdbeere]

can i ask what do you mean by splitting things up?

from where clause i did:

where
smrs.smrs_ordno = soo.soo_ordno and
pr.pr_ordno = so.soo_ordno and
smrs.smrs_ordno = so.so_ordno(+) and
ms.partno = so.so_partno(+) and
ros.ros_partno = so.so_partno(+) and
so.state != 'Closed'

which it doesn't work. it highlights the so parts..

if i do this:
where
smrs.smrs_ordno(+) = soo.soo_ordno and
pr.pr_ordno(+) = so.soo_ordno and
smrs.smrs_ordno = so.so_ordno and
ms.partno = so.so_partno and
ros.ros_partno = so.so_partno and
so.state != 'Closed'

nothing changes.

the problem i'm having is everything is in the SO table, however, i need to look for other fields in some of these tables as well.

the biggest challenge/problem is that some data happens to be in smrs, but doesn't have it in ms, ros, or soo tables.. while some of them have it in smrs and ms, which it doesn't have it in soo tables.

 

[sem]

where
smrs.smrs_ordno(+) = so.soo_ordno and
soo.soo_ordno(+) = so.soo_ordno and
pr.pr_ordno(+) = so.soo_ordno and
smrs.smrs_ordno(+) = so.so_ordno and
ms.partno(+) = so.so_partno and
ros.ros_partno(+) = so.so_partno and
so.state != 'Closed'

Regards, Dima

http://seminihin.narod.ru

 

[aryeh1010]

The where clause that Dima posted is what I meant for you to try.

If that doesn't give you a good execution time, try to split things up by inserting records from SO into another table that you create specifically for this process. Create this table with all the fields that you need for the end result, including the fields that are needed from all the other tables.

Then run updates against this new table for all the values that you have in SO, but need from the other tables, if they exist.

Aryeh Keefe