Ok, I wrote this UDF that takes a CSV and dumps it into a table. Now, I'm kind of new to DB2 and now I'm trying to figure out how to call the dang thing. Obviously you cannot call function the same way you could in MS SQL Server:
SELECT * FROM ONONEPROSP.CSV_TO_CHAR_TABLE('61701, 61702') AS A
Am I going to have to dump my results into another temp table?
What I'm expecting is a result set that returns 2 rows:
I'm using DB2 V5R2 on iSeries. I'm really just trying to debug my UDF, if it helps here's the script for my UDF.
CREATE FUNCTION ONONEPROSP.CSV_TO_CHAR_TABLE (vals CHAR(1000))
RETURNS TABLE (VAL CHAR(5))
MODIFIES SQL DATA
NO EXTERNAL ACTION
DECLARE separator CHAR(1);
DECLARE separator_position INT;
DECLARE array_value CHAR(1000);
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_VAR
ON COMMIT DELETE ROWS;
SET separator = ',';
SET vals = TRIM(vals) || ',';
WHILE POSSTR (vals, separator) <> 0 DO
SET separator_position = POSSTR(vals, separator);
SET array_value = LEFT(vals, separator_position - 1);
INSERT INTO ONONEPROSP.TEMP_VAR VALUES (array_value);
SET vals = SUBSTR(vals, separator_position + 1);
RETURN SELECT VAL FROM SESSION.TEMP_VAR;
Ok I figured it out... Didn't like me passing in CHAR to the function since I guess it considered what I was passing VARCHAR data, that or my string wasn't exactly 1000 characters long. Which makes sense. Anyway, this code has some other bugs too, but I fixed my version, maybe if anyone wants to see an updated version I'll post it.
Thanks to anyone who might have read this post and thought about helping.