AW: Elegant SQL for RPG Programmer

Hi,

I'd write a small UDF that concates the field values together and call this
UDF from the Select-Statement after:
1. UDF
CREATE FUNCTION MYSCHEMA/MYUDF 
      (PAREAN CHAR(13), PARADDNO Integer)       
       RETURNS VARCHAR(1024)            
       LANGUAGE SQL     
       NOT DETERMINISTIC        
       READS SQL DATA   
       CALLED ON NULL INPUT     
       NO EXTERNAL ACTION       
       DISALLOW PARALLEL        
   BEGIN                
       DECLARE RETURNVAL VARCHAR ( 1024 ) DEFAULT ' ' ;

       FOR CSRC1 AS C1 CURSOR           
           FOR SELECT Desc              
                  FROM MyTable                  
                  WHERE EAN = PAREAN and AddNo = PARADDNO               
           DO SET RETURNVAL = RETURNVAL CONCAT ' ' CONCAT CSRC1.Desc concat
',';
       END FOR ;                                
       RETURN LTRIM(TRIM(T ',' From RETURNVAL));
   END;

2. Using th UDF in the select-statement:
Select Distinct EAN, ADDNO, MyUDF(EAN, ADDNO)
  from MyTable

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"



-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Ian Leafe
Gesendet: Friday, February 02, 2007 07:22
An: rpg400-l@xxxxxxxxxxxx
Betreff: Elegant SQL for RPG Programmer


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.