Re: Handling multi-valued function output

Uday Kale wrote: > You should be able to use TABLE(FUNCTION(my_function(param))) construct. That works with one exception: I can not use ? for the function param in a prepared statement. If this is a documented limitation, I'm not finding it anywhere... In JDBC, using SELECT * FROM TABLE(FUNCTION(my_function(?))) gets -392 System error - unexpected null pointer encountered If I wrap the query in another function CREATE FUNCTION my_function(aDate DATE)... SELECT * FROM TABLE(FUNCTION(my_function(aDate))); ... The function will be created without error, but when I execute it I get -217 Column (adate) not found in any table in the query This appears to be problematic in both v11 and v10. Jeff > > Regds, > Uday. > > --------------------------------------------------- > Uday Kale > > E-Mail : udayk@us.ibm.com > Informix SQL Development > IBM Information Management Group > Phone: 913-599-8681 > > --------------------------------------------------- > Inactive hide details for Jeff <jlar310@yahoo.com>Jeff <jlar310@yahoo.com> > > > *Jeff <jlar310@yahoo.com>* > Sent by: informix-list-bounces@iiug.org > > 04/24/2009 04:34 PM > > > > To > > informix-list@iiug.org > > cc > > > Subject > > Handling multi-valued function output > > > > > I have a complex UDR that uses RETURN x, y WITH RESUME to > generate a list of values. > > I have found that with IDS 11, I can do > > SELECT * from TABLE(my_function(param));> > but with IDS 10, the same statement gets "-684, function returns > too many values". > > How can I work with this multi-valued function under IDS 10? > Ideally, I would like to treat it as a virtual table, joining it > to other tables, etc. > > I know I could create another UDR and use > > FOREACH execute my_function INTO varX, varY > > but I would really rather not process this data row by row. > > What are my options here? I've tried to make sense of the > documentation on collection derived tables, multisets and the > like, but the docs just don't have enough detail or practical > examples. > > Thanks, > > Jeff > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On Apr 27, 12:07 pm, Jeff <jlar...@gmail.com> wrote: > Uday Kale wrote: > > You should be able to use TABLE(FUNCTION(my_function(param))) construct. > > That works with one exception: I can not use ? for the function > param in a prepared statement. If this is a documented > limitation, I'm not finding it anywhere... > > In JDBC, using SELECT * FROM TABLE(FUNCTION(my_function(?))) > gets -392 System error - unexpected null pointer encountered > > If I wrap the query in another function > > CREATE FUNCTION my_function(aDate DATE)...> > SELECT * FROM TABLE(FUNCTION(my_function(aDate)));> ... > > The function will be created without error, but when I execute it > I get -217 Column (adate) not found in any table in the query > > This appears to be problematic in both v11 and v10. > > Jeff > > > > > Regds, > > Uday. > > > --------------------------------------------------- > > Uday Kale > > > E-Mail : ud...@us.ibm.com > > Informix SQL Development > > IBM Information Management Group > > Phone: 913-599-8681 > > > --------------------------------------------------- > > Inactive hide details for Jeff <jlar...@yahoo.com>Jeff <jlar...@yahoo.com> > > > *Jeff <jlar...@yahoo.com>* > > Sent by: informix-list-boun...@iiug.org > > > 04/24/2009 04:34 PM > > > To > > > informix-l...@iiug.org > > > cc > > > Subject > > > Handling multi-valued function output > > > I have a complex UDR that uses RETURN x, y WITH RESUME to > > generate a list of values. > > > I have found that with IDS 11, I can do > > > SELECT * from TABLE(my_function(param));> > > but with IDS 10, the same statement gets "-684, function returns > > too many values". > > > How can I work with this multi-valued function under IDS 10? > > Ideally, I would like to treat it as a virtual table, joining it > > to other tables, etc. > > > I know I could create another UDR and use > > > FOREACH execute my_function INTO varX, varY > > > but I would really rather not process this data row by row. > > > What are my options here? I've tried to make sense of the > > documentation on collection derived tables, multisets and the > > like, but the docs just don't have enough detail or practical > > examples. > > > Thanks, > > > Jeff > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list What are you really trying to do? Do you really need a function or can you use something else? Why a function being treated as a table?

Curtis Crowson wrote: > On Apr 27, 12:07 pm, Jeff <jlar...@gmail.com> wrote: >> Uday Kale wrote: >>> You should be able to use TABLE(FUNCTION(my_function(param))) construct. >> That works with one exception: I can not use ? for the function >> param in a prepared statement. If this is a documented >> limitation, I'm not finding it anywhere... >> >> In JDBC, using SELECT * FROM TABLE(FUNCTION(my_function(?))) >> gets -392 System error - unexpected null pointer encountered >> >> If I wrap the query in another function >> >> CREATE FUNCTION my_function(aDate DATE)...>> >> SELECT * FROM TABLE(FUNCTION(my_function(aDate)));>> ... >> >> The function will be created without error, but when I execute it >> I get -217 Column (adate) not found in any table in the query >> >> This appears to be problematic in both v11 and v10. >> >> Jeff >> <snip> > What are you really trying to do? Do you really need a function or can > you use something else? Why a function being treated as a table? Sure, there may be other solutions, but Informix supports the notion of function output as a virtual table. My question is valid regardless of the available alternatives: Why are parameterized queries not allowed in this case and where is the documentation that says they are not? But to answer your question, the function takes a date as input and then returns a list of identifiers (id and version no.) for services that are to take place on that date. The logic is quite complex (holidays, day-of-week, bi-weekly, one-time skip-date, etc.) This is business logic that I want stored in one and only one place. The return values of the function give just the minimal identifiers, leaving the user to join to detail tables as needed for the specific application. When the date comes to pass, we do store a record of which services were applicable on that date, but one use of the function is to peek into the future to see what will be occurring on any given date. The function is really just a foreach loop on a union of 3 queries, but the need for date input prevents me from implementing it as a view. One option which I have chosen not to implement is to create a new table with a single date column and populated it with one row for every date in the foreseeable future. Then join my selection query to the date table and filter by date, but that is no less of a hack than what I am attempting above. For now, I have got things working by skipping the use of statement parameters and instead, dynamically creating the query string with the input date as a quoted string. Jeff