Re: Inserting into a set of rows in an SPL

#1

Adam Williams wrote in message ...

Quote:

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(...);

Quote:

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) );

Quote:

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

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Inserting into a set of rows in an SPL

comp.databases.informix comp.databases.informix

Re: Inserting into a set of rows in an SPL - 06-26-2003 , 05:55 AM