ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Group_concat

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Group_concat

    Can a good soul over there show me how I can do something equivalent of MySQL GROUP_CONCAT using a DB2 function or procedure ?

    Any insights ?
    Philippe

  • #2
    Re: Group_concat

    Hi,

    the only way I'm aware of is to write your own UDFs.
    Something like this:

    PHP Code:
    CREATE FUNCTION MySchema/MyUDF (
        
    PARCol2 CHAR(5) )
        
    RETURNS VARCHAR(1024)   
        
    LANGUAGE SQL 
        NOT DETERMINISTIC 
        READS SQL DATA 
        CALLED ON NULL INPUT 
        DISALLOW PARALLEL 

       BEGIN 
          
    DECLARE ReturnVal VARCHAR(1024NOT NULL DEFAULT '';
     
          FOR 
    CsrC1 AS C1 CURSOR 
              
    FOR SELECT MyCol1 
                     FROM MyTable 
                     WHERE MyCol2 
    ParCol2 
              
    DO SET ReturnVal ReturnVal Concat CsrC1.MyCol1
          
    END FOR; 

          RETURN 
    LTRIM(ReturnVal); 
       
    END  
    This function must be called as follows:
    PHP Code:
    Select Distinct MyCol2MyUDF(MyCol2)
       
    From MyTable 
    Birgitta
    Last edited by B.Hauser; October 8, 2008, 01:26 AM.

    Comment


    • #3
      Re: Group_concat

      Birgitta,

      Very impressive solution. Thanks.

      BTW any possibility to render this UDF dynamic, i.e. something like that. I tried but no luck so far.

      PHP Code:
      CREATE FUNCTION MySchema/MyUDF  
                                      
      Table_Name varchar(10), 
                                      
      Group_Concat_Field  varchar(10), 
                                      
      Group_Field varchar(10), 
                                      
      Group_Field_Value varchar(32) )
      ...
      set sqlstm =                                               
        
      'SELECT '     concat trim(Group_Concat_Fieldconcat      
         
      ' FROM '     concat trim(Table_Nameconcat      
         
      ' WHERE '    concat trim(Group_Fieldconcat      
         
      '   =   '''      concat trim(Group_Field_Valueconcat '''';

            FOR 
      CsrC1 AS C1 CURSOR
                
      FOR sqlstm
                
      DO SET ReturnVal ReturnVal Concat CsrC1.Group_Concat_Field;
            
      END FOR; 
      ... 
      Then I could call this UDF as follows :
      PHP Code:
      Select Distinct MyUDF(MyTableMyCol2'MyCol1'MyCol1)
         
      From MyTable 
      MyCol2 is the group_concat field.
      'MyCol1' (with quotes) is the group field.
      MyCol1 (w/o quotes) is the value of the group field.

      I had a hard time trying to make it work with no success.

      Can you help ?

      Thanks in advance.
      Last edited by Mercury; October 8, 2008, 12:05 PM.
      Philippe

      Comment

      Working...
      X