Dynamic query / passing varying parameter in function call.

JennieH · #1 (**permalink**) 06-10-09, 14:20

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?

tonkuma · #2 (**permalink**) 06-10-09, 14:59

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.

JennieH · #3 (**permalink**) 06-10-09, 15:40

Thank, Jennie

Internet Services

Web Development

Digital Marketing

Consumer Tech