UDF to return delimited set

[virtualdatabase]

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

[virtualdatabase]

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?

[shawkins]

> 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

[virtualdatabase]

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!

[virtualdatabase]

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..

[shawkins]

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.

[virtualdatabase]

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?

[shawkins]

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