Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    21

    Unanswered: Returing table from if statement in function

    Hi,

    I want to return a table from function's IF statement but it won't let me do it.

    Here is the syntax i am using: -

    CREATE OR REPLACE FUNCTION Resultset(id VARCHAR(40))
    RETURNS
    TABLE(Name VARCHAR(255))
    LANGUAGE SQL
    READS SQL DATA
    NO EXTERNAL ACTION
    NOT DETERMINISTIC
    BEGIN
    IF LENGTH(id) = 0 THEN
    RETURN SELECT FolderName FROM T_FOLDER;
    END IF;

    RETURN SELECT FolderName FROM T_FOLDER WHERE Id = id;
    END
    \

    Regards.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Firstly, do not name the procedure parameters the same as table columns. Secondly, try using a single RETURN statement, you won't need the IF:
    Code:
    ...
    RETURN SELECT FolderName FROM T_FOLDER 
    WHERE length(p_id) = 0 
    or (length(p_id) > 0 and Id = p_id);
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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