-
1. Re: UDF to return delimited set
virtualdatabase Mar 29, 2013 1:44 PM (in response to 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?
-
2. Re: UDF to return delimited set
shawkins Mar 29, 2013 3:57 PM (in response to virtualdatabase)> 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 Mar 29, 2013 4:26 PM (in response to shawkins)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 Apr 3, 2013 12:12 PM (in response to 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.. -
5. Re: UDF to return delimited set
shawkins Apr 3, 2013 12:23 PM (in response to virtualdatabase)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 Apr 3, 2013 3:19 PM (in response to shawkins)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
shawkins Apr 3, 2013 6:38 PM (in response to virtualdatabase)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