UDF Returning Different SQL statements

Question

I am trying to return two different SQL statements in an UDF based on a IF condition. So far I haven't been able to do it correctly, the code below does not work. How can I do this?

CREATE FUNCTION BELSIZE.TEST (aSTRING VARCHAR(50))
    RETURNS TABLE(
    column1 INTEGER
    , column2 INTEGER   
    , column3 VARCHAR(50)
 )
 ------------------------------------------------------------------------
F1: BEGIN ATOMIC

DECLARE v_string VARCHAR(50);
SET v_string = aSTRING;

IF v_string = '123' THEN 

        With v (column1, column2, column3) as (
            VALUES (1, 10231011, 'PRINCIPAL')
            , (1, 10231012, 'ADDITIONAL')
            , (-1, 10231031, 'REDEEM')
        ) select * from v;

END IF;

RETURN
        With v (column1, column2, column3) as (
            VALUES (1, 10231011, 'PRINCIPAL 456')
            , (1, 10231012, 'ADDITIONAL 456')
            , (-1, 10231031, 'REDEEM 456')
        ) select * from v;


END

Thanks.

an33sh · Answer 1 · 2018-05-30T05:07:14.400

0

Since you need 2 different results based on the input string, your return statements should be on an IF-ELSE logic. The way you are handling it is wrong in your query. It always tries to return the one in return block.

I've corrected the query matching your requirement. use the IF-ELSE logic from that. It's written in SQL-SERVER

CREATE FUNCTION dbo.testFn(@aSTRING  VARCHAR(50))  
RETURNS @rtnTable TABLE(
    column1 INTEGER
    , column2 INTEGER   
    , column3 VARCHAR(50)
 )   
AS   
BEGIN  

    IF @aSTRING = '123'
    BEGIN
        INSERT INTO @rtnTable
        VALUES (1, 10231011, 'PRINCIPAL')
            , (1, 10231012, 'ADDITIONAL')
            , (-1, 10231031, 'REDEEM')
    END
    ELSE
    BEGIN 
        INSERT INTO @rtnTable
        VALUES (1, 10231011, 'PRINCIPAL 456')
            , (1, 10231012, 'ADDITIONAL 456')
            , (-1, 10231031, 'REDEEM 456')
    END 

    RETURN
END

edited May 30 '18 at 05:07

answered May 30 '18 at 04:57

an33sh

1,089
16
27

I had tried to do that but I am unable to do that using Data Studio for DB2. See below. – VN Khan May 30 '18 at 05:28
I had tried to do that but I am unable to do that using Data Studio for DB2. See below. `CREATE FUNCTION BELSIZE.TEST (.... IF v_string = '123' THEN RETURN With v...; ELSE RETURN With v....; END IF; END` I get the following error: BELSIZE.TEST: 15: Routine "BELSIZE.TEST" with specific name "" must have the RETURN statement as the last SQL statement of the compound body.. SQLCODE=-20148, SQLSTATE=429DB, DRIVER=3.61.65 – VN Khan May 30 '18 at 05:35
I think you should not `RETURN` anything from your `IF-ELSE` block. 1. Create a temp Table before `IF-ELSE` block. 2. Use `IF-ELSE` block to insert data into temp Table. 3. Single `RETURN` statement from temp Table after `IF-ELSE` block. – an33sh May 30 '18 at 05:50
Can you please give me a sample for the temp table? I haven't created temp table in UDF before. Thanks. – VN Khan May 30 '18 at 06:58
You can take a look [here](https://stackoverflow.com/questions/39570859/declare-temp-table-inside-userdefined-function-in-db2-as400?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) – an33sh May 30 '18 at 07:03

score 0 · Answer 2 · answered May 30 '18 at 08:06

You should specify your platform and version of Db2...

However, I don't believe it would make a difference here.

The way Db2 returns rows from a User Defined Table Function (UDTF) or a stored procedure for that matter is by ending with a cursor defined inside the function/procedure left open.

So you need something like so...

CREATE FUNCTION BELSIZE.TEST (aSTRING VARCHAR(50))
    RETURNS TABLE(
    column1 INTEGER
    , column2 INTEGER   
    , column3 VARCHAR(50)
 )

begin atomic
declare C1 cursor with return to client for
        With v (column1, column2, column3) as (
            VALUES (1, 10231011, 'PRINCIPAL')
            , (1, 10231012, 'ADDITIONAL')
            , (-1, 10231031, 'REDEEM')
        ) select * from v;

declare C2 cursor with return to client for 
        With v (column1, column2, column3) as (
            VALUES (1, 10231011, 'PRINCIPAL 456')
            , (1, 10231012, 'ADDITIONAL 456')
            , (-1, 10231031, 'REDEEM 456')
        ) select * from v;

IF v_string = '123' THEN 
  open C1;
else
  open C2;
END IF;

RETURN
END

Hi Charles, I tried this but I am getting this error in data studio: was expected to form a complete scope. — VN Khan, Jun 05 '18 at 00:37

score 0 · Answer 3 · answered May 30 '18 at 16:25

I would code your UDF something like this

CREATE FUNCTION BELSIZE.TEST (aSTRING VARCHAR(50))
    RETURNS TABLE(
      column1 INTEGER
    , column2 INTEGER   
    , column3 VARCHAR(50)
 )
RETURN
    SELECT column1, column2, column3 FROM TABLE(
        VALUES (0, 1, 10231011, 'PRINCIPAL')
        ,      (0, 1, 10231012, 'ADDITIONAL')
        ,      (0,-1, 10231031, 'REDEEM')
        ,      (1, 1, 10231011, 'PRINCIPAL 456')
        ,      (1, 1, 10231012, 'ADDITIONAL 456')
        ,      (1,-1, 10231031, 'REDEEM 456')
        ) AS v(i, column1, column2, column3)
    WHERE i = CASE WHEN aSTRING = '123' THEN 0 ELSE 1 END

UDF Returning Different SQL statements

3 Answers3