Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Dynamic query / passing varying parameter in function call.

    I work with a database where addresses are stored in a single pipe delimited field. I have written a function which returns a table of address fields. When I call it with a hard coded input parameter, I get back the expected results:

    SELECT addr.*
    FROM TABLE(ADDRESS_SPLIT('1|addr1|addr2|addr3|addr4|cit y|foo|st|zip|county|foo|1')) addr(addr1, addr2, addr3, city, st, zip, county, country);

    What I really want to do though is to call it as part of a query like so:

    SELECT n.name_id, n.f_name, n.l_name, addr.city, addr.st, addr.zip
    FROM TABLE(ADDRESS_SPLIT(n.addr_text)) addr(addr1, addr2, addr3, city, st, zip, county, country)
    JOIN name n ON 1 = 1;

    Is there a way to call the function with a field from a joined table as the input parameter?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SELECT n.name_id, n.f_name, n.l_name, addr.city, addr.st, addr.zip
    FROM name n JOIN TABLE(ADDRESS_SPLIT(n.addr_text)) addr(addr1, addr2, addr3, city, st, zip, county, country)
    ON 1 = 1;

    You can see examples in "DB2 for Linux, UNIX, and Windows SQL Reference Volume 1"
    Chapter 5. Queries ---> Subselect ---> table-reference ---> Correlated references in table-references
    Code:
    So the following examples are valid syntax:
     Example 1: SELECT t.c1, z.c5
                FROM t, TABLE( tf3(t.c2) ) AS z -- t precedes tf3
                WHERE t.c3 = z.c4;              -- in FROM, so t.c2
                                                -- is known
    Code:
    But the following examples are not valid:
     Example 4: SELECT t.c1, z.c5
                FROM TABLE( tf6(t.c2) ) AS z, t -- cannot resolve t in t.c2!
                WHERE t.c3 = z.c4;              -- compare to Example 1 above.

  3. #3
    Join Date
    Jun 2009
    Posts
    2

    That did it

    Thank, Jennie

Posting Permissions

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