× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.