7 Replies Latest reply on Apr 3, 2013 6:38 PM by Steven Hawkins

    UDF to return delimited set

    virtualdatabase Apprentice

      Anyone written a UDF to return an array of a column as a delimtited set?


      For example:

       

      select delimtted_list(col1,|) from table 1 where col = x

       

      where the query would normally return 1 or more rows would now return one row with the values from col1 delimitted by '|' (parm 2)

       

      just checking as if someone has done so maybe they'd share :-)

       

      Thanks!1

       

      BTW, I thnk this is supported by MySQL... see the following:  http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function%5Fgroup-concat

        • 1. Re: UDF to return delimited set
          virtualdatabase Apprentice

          So my first passs is this:

           

          CREATE VIRTUAL PROCEDURE

          BEGIN

                    DECLARE string VARIABLES.output1;

                    LOOP ON (SELECT convert(TECHNOLOGIST_ID, string) AS technologist_id FROM omf_radtech_order_st WHERE (ORDER_ID = 18304) AND (technologist_id <> 0) GROUP BY ORDER_ID, TECHNOLOGIST_ID) AS txncursor

                    BEGIN

                              output1 = ifnull(output1, (technologist_id || ','));

                    END

                    SELECT output1 AS technologist_id;

          END

           

          quite simple except that it only returns the 1st value wth a comma

          Am I missing something on how to loop?

          also, thinking ahead, how do I know it's the last record (and not add the ',') or is that just a function of the txncursor?

          • 2. Re: UDF to return delimited set
            Steven Hawkins Master

            > quite simple except that it only returns the 1st value wth a comma

             

            You would want something like

             

            output1 = ifnull(output1 || ',' || ifnull(technologist_id, ''), ifnull(technologist_id, ''));

             

            However you would be limited by max string length. 

             

            Other options include ...

             

            xmlagg:

             

            select xmlagg(xmlparse(content ifnull(convert(TECHNOLOGIST_ID, string), '') || ',' wellformed)) from omf_radtech_order_st ...

             

            You would then use xmlserialize to get a string/clob value back.  Although it would take a little more thought to get rid of the trailing comma.

             

            array_agg with a udf:

             

            select udf(array_agg(technologist_id)) ...

             

            where you udf could either accept object or a varargs paramter

             

            Or textagg does basically what you want but would use newlines between the values instead of ','.  Depending upon the size of the result and the values you could just do a replacement of newline to comma or you could log an enhancment for textagg to allow for configurable line endings.

             

            With all of these but textagg (depending upon if you want to use escaping) you may also have to consider the case where the delimiter appears in the value.

             

            Steve

            • 3. Re: UDF to return delimited set
              virtualdatabase Apprentice

              Intersting about the xmlagg and textagg hadn't looked into those options...

               

              here's my solution that seems to be working... (though I did change to pipe delim.)

               

              CREATE VIRTUAL PROCEDURE

              BEGIN

                        DECLARE string VARIABLES.output1;

                        CREATE LOCAL TEMPORARY TABLE TEMP1 (technologist_id string);

                        INSERT INTO TEMP1 (technologist_id) VALUES ('');

                        LOOP ON (SELECT convert(TECHNOLOGIST_ID, string) AS technologist_id FROM omf_radtech_order_st WHERE (ORDER_ID = getTechList3.Order_id) AND (technologist_id <> 0) GROUP BY ORDER_ID, TECHNOLOGIST_ID) AS txncursor

                        BEGIN

                                  UPDATE TEMP1 SET technologist_id = ((technologist_id || txncursor.technologist_id) || '|');

                        END

                        SELECT SUBSTRING(technologist_id, 1, (Length(technologist_id) - 1)) AS technologist_id FROM TEMP1;

              END

               

              Thanks for the help!

              • 4. Re: UDF to return delimited set
                virtualdatabase Apprentice

                So in my attempt to make this usable in other fashions I created the following UDF... it compiles but fails upon execution...

                Any insight would be helpful..

                 

                CREATE VIRTUAL PROCEDURE

                BEGIN

                          DECLARE string VARIABLES.TmpStr;

                          DECLARE string VARIABLES.InsTmp;

                          DECLARE string VARIABLES.loopTmp;

                          DECLARE string VARIABLES.updtTmp;

                          DECLARE string VARIABLES.selTemp;

                          VARIABLES.TmpStr = (('CREATE LOCAL TEMPORARY TABLE TEMP1 (' || Final_Penrad.DelimColumn.Focal_Column) || ') string)');

                          VARIABLES.InsTmp = ((((('INSERT INTO TEMP1 (' || Final_Penrad.DelimColumn.Focal_Column) || ') VALUES (') || chr(39)) || chr(39)) || ')');

                          VARIABLES.loopTmp = (('Loop on (' || Final_Penrad.DelimColumn.Query) || ') as txnCursor');

                          VARIABLES.updtTmp = (((((((((('          UPDATE TEMP1 SET ' || Final_Penrad.DelimColumn.Focal_Column) || ' = ') || '((') || Final_Penrad.DelimColumn.Focal_Column) || ' || ') || ' txncursor.') || Final_Penrad.DelimColumn.Focal_Column) || ') || ') || Final_Penrad.DelimColumn.Delimeter) || ')');

                          VARIABLES.selTemp = (((((('Select substring(' || Final_Penrad.DelimColumn.Focal_Column) || ', 1 , (Length(') || Final_Penrad.DelimColumn.Focal_Column) || ') -1)) As ') || Final_Penrad.DelimColumn.Focal_Column) || ' From Temp1');

                          EXECUTE IMMEDIATE VARIABLES.TmpStr;

                          EXECUTE IMMEDIATE VARIABLES.InsTmp;

                          EXECUTE IMMEDIATE VARIABLES.loopTmp;

                          BEGIN

                                    EXECUTE IMMEDIATE variables.updtTmp;

                          END

                          EXECUTE IMMEDIATE VARIABLES.selTemp;

                END

                 

                 

                The error rcvd upon execution is:

                 

                Error: Remote org.teiid.api.exception.query.QueryPlannerException: Error while planning update procedure, unknown statement type encountered: 11

                SQLState:  50000

                ErrorCode: 0

                 


                Thanks in advance..

                • 5. Re: UDF to return delimited set
                  Steven Hawkins Master

                  This is likely https://issues.jboss.org/browse/TEIID-2229 - just try to remove the nested begin/end.  You can also open a case to ensure that the fix gets backported.

                  • 6. Re: UDF to return delimited set
                    virtualdatabase Apprentice

                    Ok... progressing..

                    Now this error::

                    Error: Remote org.teiid.api.exception.query.QueryProcessingException: Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE VARIABLES.InsTmp" with the SQL statement "VARIABLES.InsTmp" due to: Group does not exist: TEMP1

                    SQLState:  50000

                    ErrorCode: 0

                     

                    Should I not be able to create the LOCAL TEMP table using exec immediate?

                    • 7. Re: UDF to return delimited set
                      Steven Hawkins Master

                      There are a couple of issues here.  The first is that execute immediate executes in it's own scope and so a temp table created inside will not escape.  The alternative is to use execute immediate with an INTO clause.

                       

                      The next is that execute immediate simply executes in place - you cannot use it to dynamically declare a loop curor and attempt to iterate over it.  The alternative here again is to use execute immediate with an INTO clause and then iterate over the temp table.  You could also log an enhancement request to allow execute immediate to accept arbitrary procedure statements including blocks.

                       

                      It would also seem though that the use of temp table here in general does may not be necessary as all you are doing is accumulating a string result.

                       

                      Steve