If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Pass a set of values into a udf?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2008
Posts: 23
Pass a set of values into a udf?

Hi,

I have a recursive query that I'd like to parametrize. The query is kind of long so I'll just give a basic example.


Code:
with temp(c1, c2, c3) as (
select a, b, c from table
-- Need to set parameters here -- where a in (5, 6, 7, 8, 9)
union all
select c1, c2, c3 from temp
where not terminator)
select * from temp
-- Can't do this - where c1 in (5, 6, 7, 8, 9)
The reason I need to put the params in the base case of the sql is probably obvious. The table has millions of rows and if I don't parametrize at the base, its going to recurse the whole table and then try to apply the filters.

So it looks like my option is to create a UDF or stored procedure and then take the parameter and apply it to a where in the base case of the recursion. I can easily do that if all I wanted to filter on was one value, but I want to do a "where in".

So 2 questions:

1 - Is there a way to pass a variable sized set of values into one argument of a table UDF or stored procedure?

2 - Are there any alternative methods to filtering a recursive query?

One way I'm thinking is to pass a delimited string to a stored procedure. Then parse the string and pass the values into a temp table. Then in the procedure or udf, do a "where in" on the temp table. Outside of that, I'm stumped. Any ideas are appreciated.
Reply With Quote
  #2 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,332
Not sure that I fully understand your problem but you could try using the ARRAY data type introduced in DB2 9.5 - you can pass ARRAYs to SQL stored procedures, according to the documentation.
__________________
---
"It does not work" is not a valid problem statement.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2008
Posts: 23
Yeah - I've found info on using arrays. Problem is our DB isn't 9.5.

I haven't found out whether or not I can use an array within sql. In other words, could the SQL have something like

"where key in myarray"

The problem is that in order to filter recursive SQL, as far as I can tell, if you want it to perform the filter has to be in the base case (before the union all). Since we don't want to give our user the actual sql, we have to give them a table udf or a stored procedure in order to allow the injection of a filter into the base case (again, as far as I know).

Thanks,
Joe
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On