Re: Calling a SPL that returns LIST(...)

> > In the field clause of an SELECT statement one can call a SPL such as - > > SELECT ....> > p_get_ref_cost("ME", xr_oem_code, xr_oem_partcode) as ref_cost > > ... > > - and get that value back for each row. It works great so long as the > > SPL returns ONE value (in the above case a FLOAT). > > Is it possible, and if so how, to call a SPL in the same manner that > > returns multiple values. > > The signature looks like - > > CREATE PROCEDURE p_get_ref_list(i_company_id char(2), > > i_oem_code char(4), > > i_oem_partcode char(20)) > > RETURNING CHAR(4), FLOAT;> > - I'd like to do something like - > > SELECT ... > > p_get_ref_list("ME", xr_oem_code, xr_oem_partcode) as (ref_vendor, > > ref_price)> > ... > > - only that doesn't work. > You don't give us any information about your system, but if you are using 9.40.UC2 Linux > IDS 9.x, you could look into using ROW types. The IBM Informix Guide to SQL > manuals describe the creation and use of these complex data types. (If you > are using IDS 9.x, you should also be creating a function rather than a > procedure. See the SQL manuals for a definition and complete description of > the two.) > If you decide to use a ROW type, you could save yourself some time by > reading up on error -696 (finderr 696). Pay particular attention to the > section the describes initializing the variable. The details given for this > error include an example where a row type is created and used within a > function. I'll look that up. Thanks. > One other suggestion comes to mind and that would be to create two function > to be called within your SELECT, one to return the CHAR(4) and the other to > return the FLOAT.... The trouble is I can't be absolutely certain if I call the routine twice that I will get a corresponding pair of values. sending to informix-list