Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2015
    Posts
    2

    Unanswered: Function returning table

    Hi I am new to DB2 and want to create a function which returns a query result. Later I will be selecting from this function to fetch a report .

    CREATE TABLE TMP.EMP (E_ID INTEGER, E_NAME VARCHAR(10), E_ADDRESS VARCHAR(10));

    INSERT INTO TMP.EMP (E_ID,E_NAME,E_ADDRESS) VALUES (1,'AAA','ABCDEF');
    INSERT INTO TMP.EMP (E_ID,E_NAME,E_ADDRESS) VALUES (2,'BBB','ABCDEF');
    INSERT INTO TMP.EMP (E_ID,E_NAME,E_ADDRESS) VALUES (3,'CCC','ABCDEF');
    INSERT INTO TMP.EMP (E_ID,E_NAME,E_ADDRESS) VALUES (4,'DDD','ABCDEF');

    Create a Function which take E_ID as input parameter. This should be null if user doesn't provide the value of E_ID.
    In the function
    IF E_ID is null, then RETURN SELECT * FROM EMP;
    ELSE RETURN SELECT * FROM EMP WHERE E_ID = v_E_ID;
    END IF;

    Can anyone help me with this please...

    Thanks in Advance

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Help youself by studying the IBM example code for table functions in the link below
    (also available in your SAMPLES directory of your unspecified DB2 version on your unspecified operating system)
    http://www-01.ibm.com/support/knowle...3.html?lang=en

  3. #3
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    read this. It's actually for I series but is still pertinent
    http://www.ibm.com/developerworks/ib...power-of-udtf/
    Last edited by tafster; 08-31-15 at 21:57.
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

Tags for this Thread

Posting Permissions

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