Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: udf returning a SQL Table

    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:

    61701
    61702

    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))
    LANGUAGE SQL
    MODIFIES SQL DATA
    NO EXTERNAL ACTION
    DETERMINISTIC
    DISALLOW PARALLEL
    BEGIN
    DECLARE separator CHAR(1);
    DECLARE separator_position INT;
    DECLARE array_value CHAR(1000);
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_VAR
    (VAL CHAR(5))
    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);
    END WHILE;
    RETURN SELECT VAL FROM SESSION.TEMP_VAR;
    END

  2. #2
    Join Date
    Oct 2003
    Posts
    2

    Re: udf returning a SQL Table

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •