I've created a routine that returns a MUTLISET so we can encapsulate a
rather complicated query in a SPL. However it keeps producing an
exception -9600 at the end and this returning an empty set. -9600 isn't
in my documentation so I don't really know where to go looking. We've
done this kind of thing before so maybe it is just a brain fart.

A sample query looks like....
SELECT vendor FROM TABLE(p_oem_vendors("ME", "009"))
.... and the SQL inside the routing works and always returns at least one
row. The routine itself is....

CREATE PROCEDURE p_oem_vendors(i_company_id CHAR(2), i_oem_code char(4))
RETURNING MULTISET (ROW(vendor char(4)) NOT NULL);

DEFINE v_vend_code CHAR(4);
DEFINE v_error INT;
DEFINE v_message VARCHAR(100);
DEFINE v_return MULTISET (ROW (vendor char(4)) NOT NULL);
DEFINE v_count INT;

ON EXCEPTION SET v_error
INSERT INTO exceptions
(error_code, routine, message)
VALUES
(v_error, 'p_oem_vendors', v_message);
END EXCEPTION;

LET v_count = 0;
LET v_message = "Selecting Vendors For OEM " || i_oem_code;
INSERT INTO exceptions(error_code, routine, message)
VALUES(0, 'p_oem_vendors', 'Start');

FOREACH
SELECT ve_vend_code
INTO v_vend_code
FROM vendorr
WHERE ve_vend_code IN
(SELECT vendor_code FROM vendor_acl WHERE company_code =
i_company_id)
AND ve_vend_code IN
(SELECT v1.ve_vend_code
FROM vendorr v1
WHERE v1.ve_oem_code IN
(SELECT v2.ve_oem_code
FROM vendorr v2, oemr o1
WHERE v2.ve_vend_code = o1.oe_oem_vndr_code
AND oe_oem_code = i_oem_code))

INSERT INTO exceptions(error_code, routine, message)
VALUES(0, 'p_oem_vendors', 'Found vendor ' ||v_vend_code|| ' for
OEM ' || i_oem_code);

INSERT INTO TABLE(v_return)
VALUES (v_vend_code);

LET v_count = v_count + 1;

END FOREACH;

INSERT INTO exceptions(error_code, routine, message)
VALUES(0, 'p_oem_vendors', v_count || ' records found');

RETURN v_return;
END PROCEDURE;
--------------------------------------------------
My "exceptions" table contains..
record_id:error_time:error_code error_user:routine:message
101:2005-08-22 09:59:31.0:0:adam_oem_vendors:Start
102:2005-08-22 09:59:31.0:0:adam_oem_vendors:Found vendor 320 for OEM
009
103:2005-08-22 09:59:31.0:0:adam_oem_vendors:Found vendor 348 for OEM
009
104:2005-08-22 09:59:31.0:0:adam_oem_vendors:2 records found
105:2005-08-22 09:59:31.0:-9600:adam_oem_vendors:Selecting Vendors For
OEM 009
.... for each time the routine is run.
sending to informix-list