Help on repeated lines/duplicates values or use of outer join!!!!!!!!!

[sommererdbeere]

Hi,

I always have problems with duplicated/repeated lines. for my sql, i have to join 6 tables to retrieve data.

1st table: so_operation
wc ord# parpart# qty
fj 10023 ws20004 0
fj 10023 ws20004 0

2nd table: so
ord# parpart# qty
10023 ws20004 0

3rd table: so_material
ord# comppart# qtyreq
10023 pw20031 25

4th table: manu_std
parpart# comppart
ws20004 pw200031

5th table: pur_req_line
ord# req#
10023 4337
10023 5123

6th table: routing
parpart# cost
ws20004 0.26

sql statement:
select
soo.wc wc,
soo.ord# ord#,
pr.ord# prord# ,
soo.part# parpart#,
smrs.part# comppart#,
soo.qty soo_qty,
ros.part# ros_part# ,
from so_operation SOO, so so,
so_material SMRS, manu_std ms,
pur_req_line pr, routing ros
where smrs.ord# = soo.ord# and
pr.ord# = soo.ord# and
smrs.ord# = so.ord# and
ms.part# = soo.part# and
ros.part# = soo.part# and
so.state != 'Can'


from the above sql, it gives me the following output:

wc ord# prord# parpart# comppart# soo_qty ros_art# req#
fj 10023 10023 ws20004 pw20031 0 ws20004 4337
fj 10023 10023 ws20004 pw20031 0 ws20004 5123
fj 10023 10023 ws20004 pw20031 0 ws20004 4337
fj 10023 10023 ws20004 pw20031 0 ws20004 5123

the bottom 2 lines are repeated..

if anybody can help me on this, it will be great & awesome, because i try to fidn the solution to this for more than a day already..

million thanks for those who help me..

many many many thanks

m

 

[avjoshi]

Hi,

I haven't reay looked into your table structures or inspected the SQL statement; but you may try thin,

select distinct
soo.wc wc,
soo.ord# ord#,
pr.ord# prord# ,
soo.part# parpart#,
smrs.part# comppart#,
soo.qty soo_qty,
ros.part# ros_part# ,
from so_operation SOO, so so,
so_material SMRS, manu_std ms,
pur_req_line pr, routing ros
where smrs.ord# = soo.ord# and
pr.ord# = soo.ord# and
smrs.ord# = so.ord# and
ms.part# = soo.part# and
ros.part# = soo.part# and
so.state != 'Can';

Let us know if that helps you.


Anand

 

[sommererdbeere]

Hi,

thanks for ur reply.. when i use distinct, i still have 4 output, but it got organized as:

wc ord# prord# parpart# comppart# soo_qty ros_art# req#
fj 10023 10023 ws20004 pw20031 0 ws20004 4337
fj 10023 10023 ws20004 pw20031 0 ws20004 4337
fj 10023 10023 ws20004 pw20031 0 ws20004 5123
fj 10023 10023 ws20004 pw20031 0 ws20004 5123

do u have any other suggestions or does anybody have any suggestions.. this is very URGENT..

please, please, please help!!!!!!!!

 

[sommererdbeere]

Hi,

i narrow down the problem.. when the i have
smrs.ord# = soo.ord# and
pr.ord# = soo.ord# and
together in the sql statement, the data got repeated itself:


1st table: so_operation
wc ord# parpart# qty
fj 10023 ws20004 0
fj 10023 ws20004 0


5th table: pur_req_line
ord# req#
10023 4337
10023 5123


select distinct
soo.wc wc,
soo.ord# ord#,
pr.ord# prord# ,
soo.part# parpart#,
smrs.part# comppart#,
soo.qty soo_qty,
ros.part# ros_part# ,
from so_operation SOO, so so,
so_material SMRS, manu_std ms,
pur_req_line pr, routing ros
where smrs.ord# = soo.ord# and
pr.ord# = soo.ord# and
smrs.ord# = so.ord#

does anybody have any idea as to why the data got repeated?? many many many thanks

m

 

[goranm]

Hi !

What is the difference of the two lines you show from the so_operation table ?

For me they look the same.
Does the table have any other column that make them unique ?

Otherwise as you say in your previous post
smrs.ord# = soo.ord# and
pr.ord# = soo.ord# and
will get you duplicates as each line in the pr table with ordernumber 10023 will match two lines in the soo table.

/Goran

 

[sommererdbeere]

the req# is different.. there are 2 different req#: 4337 & 5123, which each of them repeated once...

i still don't understand why the fact it got repeated.. does any of u have any idea on how to appoarch this problem? many thanks

 

[goranm]

Hi again !

Don´t you have the req# column in the soo table as well?

In that case add another line to your statement like:

pr.ord# = soo.ord# and
pr.req# = soo.req#

/Göran

 

[dbtoo2001]

I'll venture an opionion...
I believe you are seeing 'duplicates' because you have two records of identical information in your

1st table: so_operation
wc ord# parpart# qty
fj 10023 ws20004 0 fj 10023 ws20004 0 / These two are identical

Your query joins each row of so_operation, with the appropriate information in the other tables, yielding two records for each row in so_operation. I would assume that there is no primary key on so_operation allowing this to occur. You would need to have a statement in your WHERE clause which will look for only unique entries in the so_operation table.

add to you query something like
AND (soo.wc||soo.ord#||soo.parpart#||soo.qty) in (
select distinct soo1.wc||soo1.ord#||soo1.parpart#||soo1.qty
from so_operation soo1) or something that would generate uniqueness for the records.

 

[carp]

If that is the only place where you have duplicate rows, then

select
soo.wc wc,
soo.ord# ord#,
pr.ord# prord# ,
soo.part# parpart#,
smrs.part# comppart#,
soo.qty soo_qty,
ros.part# ros_part# ,
from
(SELECT DISTINCT wc, ord#, parpart#, qty
FROM so_operation) SOO,
so so,
so_material SMRS, manu_std ms,
pur_req_line pr, routing ros
where smrs.ord# = soo.ord# and
pr.ord# = soo.ord# and
smrs.ord# = so.ord# and
ms.part# = soo.part# and
ros.part# = soo.part# and
so.state != 'Can' ;

might get you around the immediate problem. While this isn't a great technique that will get you through every problem, it might get you over the hump in time to take care of your urgent situation.
There IS still the question of these two identical rows. dbtoo2001 is exactly right: you are getting repeating results because of these rows. Would it hurt anything if you deleted one of them? That would take out the repeating results and alleviate similar problems in the future.

 

[sommererdbeere]

thanks for all of your reply.. it is a great help for me.. ehehheehheehe.. finally have the problem solved.. thank u thank u

 

[sommererdbeere]

hi, i have the same problems again for duplication. is there any way i can resolve it besides outer join?

i have 2 tables, but the data got repeated when i put it together and there is only 1 field from both table that has similarity.

i'm using oracle 8 as database

table 1: cust
pn cust_no qty
pp1234 12013 90000
pp1234 12013 3355
pp1234 12022 6788
pp1234 12066 2545
pp1234 12066 234


table 2: invent
pn loc lot qtyon
pp1234 HK hp234 4000
pp1234 HK hp234-9 300

my sql:
select
c.pn c_pn,
c.cust_no c_cust_no,
c.qty c_qty,
i.pn i_pn,
i.loc i_loc,
i.lot i_lot,
i.qtyon i_qtyon
from cust c, invent i
where c.pn(+) = i.pn

the outcome i got:

c_pn c_cust_no c_qty i_pn i_loc i_lot i_qtyon
pp1234 12013 90000 pp1234 HK hp234 4000
pp1234 12013 3355 pp1234 HK hp234 4000
pp1234 12022 6788 pp1234 HK hp234 4000
pp1234 12066 2545 pp1234 HK hp234 4000
pp1234 12066 234 pp1234 HK hp234 4000

pp1234 12013 90000 pp1234 HK hp234-9 300
pp1234 12013 3355 pp1234 HK hp234-9 300
pp1234 12022 6788 pp1234 HK hp234-9 300
pp1234 12066 2545 pp1234 HK hp234-9 300
pp1234 12066 234 pp1234 HK hp234-9 300

from the above, i've separate an empty space to tell taht the bottom one or the above one is repeated.

please help.. i've try using partition, but in this case, it won't work 'coz some of the customer no might be repeated.

thank u thank u for those who help.. please help me for those who knew.. tahnk u thank u

m

 

[sem]

Please don't post duplicate questions. Next time they will be red-flagged.

Regards, Dima

http://seminihin.narod.ru

 

[sommererdbeere]

okai. sorry