Function DB2 Table Function

Question

Does someone knows how can I get that result bellow in DB2?

Query

Example : 

    SELECT * FROM TABLE(FunctionName('1||2||3',||)) ;

First one are parameters with values and second one is delimiter.

Result:

    Column

    1
    2
    3

Values that I put on that query are based on parameters and delimiters.

As I said above, rows is break by Pipe. I dont have huge background on PL/SQL.

Im using DB2 v11.1

Do you mean ` SELECT * FROM TABLE(FunctionName('1||2||3','||')) ;` — mao
– mao, Commented Dec 27, 2017 at 13:39
Possible duplicate of stackoverflow.com/questions/18961996/… — mao
– mao, Commented Dec 27, 2017 at 13:40

Luiz · Accepted Answer · 2017-12-27 15:55:39Z

1

So ive been facing that problem , and i found a solution for it :

CREATE OR REPLACE FUNCTION Test(
Data_1 CLOB(1M), Delimtator VARCHAR(12))
RETURNS TABLE (
FieldData varchar(2048))
LANGUAGE SQL
BEGIN
    DECLARE dInic INTEGER DEFAULT 1 ;
    DECLARE dFim  INTEGER DEFAULT 0 ;
    DECLARE Rowid1 INTEGER DEFAULT 0;
    DECLARE Campo VARCHAR(2048);

    IF Data_1 IS NULL THEN
        RETURN;
    END IF;

    SET dFim=LOCATE(Delimtator,Data_1);

    WHILE dFim>0 DO
     SET Campo=SUBSTRING(Data_1,dInic,dFim-dInic);
        PIPE (Campo);
        SET dInic=dFim+LENGTH(Delimtator);
        SET dFim=LOCATE(Delimtator,Data_1,dFim+LENGTH(Delimtator));
    END WHILE;

    SET Campo=SUBSTRING(Data_1, dInic,LENGTH(Data_1)-dInic+1);
    PIPE (Campo);
    RETURN;
END@

answered Dec 27, 2017 at 15:55

Luiz

1412 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

danny117 Over a year ago

I like that you did this. Could you comment the code.

Collectives™ on Stack Overflow

Function DB2 Table Function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related