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. sending to informix-list

"Adam Tauno Williams" <adam@morrison-ind.com> wrote: > > 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 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. 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.... HTH -- June Hunt

Adam Tauno Williams wrote: Is the problem returning more than one row or more than one element per return value? If the former, the SPL has to include the WITH RESUME clause in the RETURN statement so that the procedure retains control after the return and is queried for the next row. Processing continues immediately after the return with resume so it is usually included in some loop like a FOREACH. If the latter, read on: If you ONLY need back the results of the procedure and don't need to join it to other data, you can: EXECUTE PROCEDURE p_get_ref_cost(...); If you need to include info from tables in the select, then you have one of two choices depending on what version of IDS you are using. If you have 9.xx you can cast the function result to a ROW type: CREATE ROW TYPE ret_from_p_get_ref_list( some_char char(4), some_float float ); SELECT r.*, ret_from_p_get_ref_list::p_get_ref_list(...) FROM referals r; If what you want is to have the procedure run with parameters based on the values in the other tables in thw query, then IB you'll have to use a sub-query. Art S. Kagel > 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. > > sending to informix-list