Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Posts
    7

    Unanswered: Parmater list from function

    I have the following SQL statement

    SELECT BLMCU,E1USER.BRANCHPLANT('4421,5001') Test FROM CRPDTA."F5549119"
    where BLMCU IN (E1USER.BRANCHPLANT('4421,5001'))

    which returns no records

    but the following SQL does

    SELECT BLMCU,E1USER.BRANCHPLANT('4421,5001') Test FROM CRPDTA."F5549119"
    where BLMCU IN (' 4421',' 5001')



    the E1USER.BRANCHPLANT('4421,5001') is a function which takes string parameter and returns a string like so ' 4421',' 5001'
    which is the same as the text I key in for the second query


    Why the second query returns records and the first one does not?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your function is returning a single string (one item)--does not matter what is in it, it is still only one item. You are thinking it as a list of two items, but it is being used as a list of one item in the IN clause. It would have to return a table to work the way you want it.

    Andy

  3. #3
    Join Date
    Jan 2007
    Posts
    7
    Is there a way to return a Table / Recordset from a Function?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see these articles.

    Parsing Strings in SQL

    http://www.dbforums.com/db2/1674552-...procedure.html

    Aren't they useful for your requiremens?

  5. #5
    Join Date
    Jan 2007
    Posts
    7
    Thank You very much.

    This helps

Posting Permissions

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