Re: Elegant SQL for RPG Programmer

I believe this would work... or at least close.


Select distinct EAN, ADDNO,  F01.DESC  || ',' || F02.DESC

From webtest1 as F01

Join webtest1 as F02 on F01.ean = F02.ean
              and F01.addno = F02.addno
                                     and f01.desc <> f02.desc


This will likely only work if there are two records in the table with
different descriptions.  You may want to look into recursive SQL.  I don't
believe there's a true recursive SQL, but I believe a colleague here found
something that worked well enough for him.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
For hotcake tips, interviews with our chefs and more, check out the Stacked
and Stuffed blog at http://imstuffed.blogspot.com


rpg400-l-bounces@xxxxxxxxxxxx wrote on 02/02/2007 01:21:46 AM:

Dear All,
            wonder if anyone can help?

            A colleague constructed some SQL below and asked if there
was a more elegant solution.

            As I'm an aged RPG programmer wasn't of much help I'm
afraid.

           Anybody got a better solution?

           All input gratefully received.

Best Regards

Ian

PS Apologies beforehand if this is simple and I'm being simple.

***************************************************************************­

************************************
Hi Ian,

Here was the messy solution...

select distinct EAN,
ADDNO,
 strip(rtrim('F58754 - ' ||  (case when (select count(*) from webtest1
as F02 where F01.ean = F02.ean and F01.addno = F02.addno and NO = 1) =
1
then (select desc from webtest1 as F02 where F01.ean = F02.ean and
F01.addno = F02.addno and NO = 1) || ', ' else '' end )
 || (case when (select count(*) from webtest1 as F02 where F01.ean =
F02.ean and F01.addno = F02.addno and NO = 2) = 1
then (select desc from webtest1 as F02 where F01.ean = F02.ean and
F01.addno = F02.addno and NO = 2) || ', ' else '' end )
 || (case when (select count(*) from webtest1 as F02 where F01.ean =
F02.ean and F01.addno = F02.addno and NO = 3) = 1
then (select desc from webtest1 as F02 where F01.ean = F02.ean and
F01.addno = F02.addno and NO = 3) || ', ' else '' end )
 || (case when (select count(*) from webtest1 as F02 where F01.ean =
F02.ean and F01.addno = F02.addno and NO = 4) = 1
then (select desc from webtest1 as F02 where F01.ean = F02.ean and
F01.addno = F02.addno and NO = 4) || ', ' else '' end )
 || (case when (select count(*) from webtest1 as F02 where F01.ean =
F02.ean and F01.addno = F02.addno and NO = 5) = 1
then (select desc from webtest1 as F02 where F01.ean = F02.ean and
F01.addno = F02.addno and NO = 5) || ', ' else ''
end )),T,',')
from webtest1 as F01

Webtest1 looks like this

no  ean addno  desc
1 503 11 Ship From
1 504 22 Ship From
2 505 33 From Role
2 503 11 From Role
5 504 22 Ship To

the messy sql outputs something like this

ean  addno  desc
503 11  Ship From, From Role
504 22  Ship From, Ship To
505 33  From Role

Cheers,
Anita.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.