Inserting into a set of rows in an SPL

I have a "matrix" of values I need to pass between stored procedures for various queries. I can create a LIST type, insert values, return it, query the result, etc... so long as the list/set has only one value. If I try to LIST (ROW (...) NOT NULL) I can't figure out how to insert values into it. I haven't unearthed any example, which makes me wonder if it is even possible. For example DEFINE MULTISET (ROW ( oem_code char(4), captive float, noncaptive float ) NOT NULL ); FOREACH select oem_code, captive_price, non_captive_price into v_oem_code, v_captive, v_noncaptive from cust_price where company_code = i_company_id and cust_price_code in ('DEFAUL', i_customer_id) {{{{ HOW DO I INSERT VALUES INTO THE SET? }}}} END FOREACH; RETURN matrix; If I try a simple INSERT INTO TABLE(matrix) VALUES (v_oem_code, v_captive, v_noncaptive), but I get a wrong number of values error. I'm thinking I need some funny casting type syntax. sending to informix-list

Adam Williams wrote in message ... > >I have a "matrix" of values I need to pass between stored procedures for >various queries. > >I can create a LIST type, insert values, return it, query the result, >etc... so long as the list/set has only one value. > >If I try to LIST (ROW (...) NOT NULL) I can't figure out how to insert >values into it. I haven't unearthed any example, which makes me wonder >if it is even possible. > >For example > >DEFINE MULTISET (ROW ( oem_code char(4), > captive float, > noncaptive float > ) NOT NULL ); You need to give it a name: DEFINE matrix MULTISET(...); > >FOREACH select oem_code, captive_price, non_captive_price > into v_oem_code, v_captive, v_noncaptive > from cust_price > where company_code = i_company_id > and cust_price_code in ('DEFAUL', i_customer_id) > > {{{{ HOW DO I INSERT VALUES INTO THE SET? }}}} INSERT INTO TABLE(matrix) VALUES ( ROW(v_oem_code, v_captive,v_nocaptive) ); > >END FOREACH; > >RETURN matrix; > >If I try a simple INSERT INTO TABLE(matrix) VALUES (v_oem_code, >v_captive, v_noncaptive), but I get a wrong number of values error. I'm >thinking I need some funny casting type syntax. Yes, you're trying to insert 3 values when it only expects 1 row (of 3 values). You don't need to cast provided that v_oem_code is a char(4) and v_[non|0]captive are floats. -- RH