How to merge 3 tables using UNION??? URGENT!!

[sommererdbeere]

To all experts,

hi, i try to use UNION and i can't get the function right. i really really hope i can get help from any of the experts here, please help..

from the sql statement
select
coj.ord#,
coj.part#,
coij.coi_part#,
coj.line#,
coij.coi_line#,
coj.redqty,
coij.coi_invq
from co_join coj, coaddress cocae, coinvoice coij
where coij.ord# = coj.ord#(+) and
cocae.CUST_ID = coj.CUST_NO and
cocae.ADD_ID = coj.addr_NO and
coj.CONTRACT='SU' and coj.ORD_ID = 'NO' and
coj.STATE !='Can'

i had, the output of the data will be like this:
data:

ord# part# coi_part# line# coi_line# redqty coi_invq
100 10012 10012 1 1 1000 1000
100 10013 10012 2 1 2000 1000
100 10014 10012 3 1 3000 1000
100 10012 10013 1 3 1000 3000
100 10013 10013 2 3 2000 3000
100 10014 10013 3 3 3000 3000


as it can be seen, there are 3 lines repeated.. i try to use (+) as coj.part#(+) = coij.part#, it will give me 2 lines instead of 3 lines.. i want the output to look sth like:

ord# part# coi_part# line# coi_line# redqty coi_invq
100 10012 10012 1 1 1000 1000
100 10013 10012 2 1 2000
100 10014 10013 3 3 3000 3000

i only want 3 lines, however,for coi_invq, i only want 1000 and 3000 'coz there are 2 invoices in the system

does anyone have any idea as to how to appoarch this? will UNION do for this situation beause it can eliminate duplicates?

many many many thanks

 

[SantaMufasa]

Sommer,

The reason you haven't received any responses yet, despite the urgency in your thread header may be due to our not being able to intuitively understand the data transformation you present. (When you say, "as it can be seen, there are 3 lines repeated", I frankly cannot see the 3 lines that are repeating.)

If you can re-attempt to explain the data transformation you desire, we can probably help you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 02:12 (23Jan04) GMT, 19:12 (22Jan04) Mountain Time)