Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: Function With In list parameter

    Hi i am trying to return a table from function my input parameter is list, i am not getting result set, query returns no data
    Example

    CREATE FUNCTION test (para char(100))
    RETURNS TABLE (col1 char(7) )
    NO EXTERNAL ACTION
    F1: BEGIN ATOMIC
    RETURN select col1 from admin.table1 where col2 in (para) ;
    END
    para = 100,101

    result 0 records

    expected result
    Col1
    A
    B

    if para = 100

    result
    Col1
    A

    if para = 101

    result
    Col1
    B

    Thanks

    I am using DB2 v9.5

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by phil72 View Post

    expected result
    Col1
    A
    B
    Why would you expect that?


    Quote Originally Posted by phil72 View Post
    para = 100,101
    Wrong. para = '100,101' (with 93 trailing blanks, by the way, because you used CHAR instead of VARCHAR). I hope you know what a character string is.

    As a result, your query looks like this:
    Code:
    select col1 from admin.table1 where col2 in ('101,102               ')
    Consider using dynamic SQL. Check EXECUTE IMMEDIATE in the manual.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If no comma(',') in col2 was guaranteed,
    try

    CREATE FUNCTION test (para char(100))
    RETURNS TABLE (col1 char(7) )
    NO EXTERNAL ACTION
    RETURN select col1 from admin.table1 where LOCATE(col2 , para) > 0
    ;

    "BEGIN ... END" is not necessary.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by tonkuma View Post
    select col1 from admin.table1 where LOCATE(col2 , para) > 0
    Although this is logically correct, how likely is that that you'd use this in real life as opposed to a dynamically constructed statement? I'm sure you realize potential performance issues.

  5. #5
    Join Date
    Nov 2008
    Posts
    48
    Thanks for the reply, the dynamic sql did not work. but locate is working fine.

Posting Permissions

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