Re: Is this possible in SQL

Note to self: it even works when PRODUCT and TEXT are different sizes (10A
and 20A respectively):

ORDERNO   LINENO   REST
    123       10   PROD1
    123       11   TEXT 1
    123       12   TEXT 2
    123       20   PROD2
    123       30   PROD3
    123       31   TEXT 3

Peter





"Peter Colpaert" <Peter.Colpaert@xxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/06/2004 11:09
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re: Is this possible in SQL






Oliver,

if PRODUCT and TEXT are the same length, you could

select orderno, lineno, rest from (select ord as orderno, lin as
lineno, product as rest from filea union select ord as orderno, lin
as lineno, text as rest from fileb) as table1
order by orderno, lineno, rest

That should produce the result you need.

Maybe casting can be used if product and text are not the same length...

HTH,

Peter Colpaert
iSeries Application Developer

Honda Europe NV
Langerbruggestraat 104
B-9000 GENT
Belgium
Peter.Colpaert@xxxxxxxxxxxx
Tel: +32 9 2501 334
Fax: +32 9 2501 231
----------
Yoda of Borg are we: Futile is resistance. Assimilate you, we will.
----------




oliver.wenzel@xxxxxxxxxxxxxxxxxxxxxxx
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/06/2004 11:03
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

Subject
Is this possible in SQL






Hi,

we have an order line file and an order line text file like:

ORD LIN PRODUCT   (filea)
123     10       ITEM

and


ORD  LIN  TEXT    (fileb)
123      11    textaaaa
123      12    more text


Is it possible to join both files in SQL and get the following output:

ORD   LIN
123       10         ITEM              (from filea)
123       11         textaaaa       (from fileb)
123       12        more text       (from fileb)

123       20         ITEM2

123      30         ITEM3
123      31         text


Regards,

Oliver





The information contained in this communication is confidential and may be 
legally privileged. It is intended solely for the use of the individual or the 
entity to whom it is addressed and others authorised to receive it. If you have 
received it by mistake, please let the sender know by e-mail reply and delete 
it from your system.
If you are not the intended recipient you are hereby notified that any 
disclosure, copying, distribution or taking any action in reliance of the 
contents of this information is strictly prohibited and may be unlawful.
Honda Europe NV is neither liable for the proper and complete transmission of 
the information contained in this communication nor for any delay in its 
receipt.