How to handle DUPLICATED LINES???!!! REALLY REALLY NEED HELP!! PLS..

[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]

What result do you need? I can not see any duplication because some fields are different thus that rows are not similar.

Regards, Dima

http://seminihin.narod.ru

 

[carp]

I agree with Dima - there don't appear to be any duplicate rows in your example!

 

[sommererdbeere]

there is duplication.. the result i got from my sql statement becomes:
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

i have separate a space in between to identify the 5 lines got repeated once.

it will be great if any of you can help me on this.

million million thanks

m

 

[Turkbear]

Hi,
What are we missing?
These Lines:

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


are NOT the same as these:


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


hp234 and hp234-9 are different, for instance.

 

[sommererdbeere]

from my very first question i asked, i included 2 tables that i'm using (i copy it again as follow):
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

as you can tell, there are only 2 lots.. howver, it got repeated 8 times.

i was looking on the cust_no to c where do they repeated from.

i separate a white space fromt he outcome, becuase the "cust_no" are being repeated whereas other data are being also repeated.

thank u thank u for those who help..

m

 

[dbtoo2001]

the plus sign has to be associated with the table that has the fewer values. Your query asked oracle to pair everything in table c with table i. I think what you want is to pair everything in table i with table c.

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(+)

 

[sommererdbeere]

i've tried both ways, but the outcome is the same.

the reason i use (+) for c.pn, because there can be multiple lines for c.pn, while there might not be mulitple for i.pn

pls pls advice me if anyone knows

for now, i have try:

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
(select distinct cust_no, pn from cust) c,
(select distinct pn, loc from invent) i
where c.pn(+) = i.pn

this works, it will give me no repeated line outcomes, however, if i ahve i.qtyon included,

from
(select distinct cust_no, pn from cust) c,
(select distinct pn, loc, qtyon from invent) i
where c.pn(+) = i.pn

the data will get repeated, because i think qtyon has 2 different value.

pls pls help and thank you for those who help.. thank you

m