I'am trying to speed up our crystal reports by using stored procedures but I had never heard of stored procedures until a couple of days ago. I have managed to create a stored procedure over 1 table selecting multiple records but can't get it to work over 2 tables. The code for 1 table is below which was written in DBACCESS. The version of Informix is IDS 7.3 tc7 on an NT 4 platform
CREATE PROCEDURE cusdef_ilc401 (order INTEGER, line INTEGER)
RETURNING INTEGER, INTEGER, CHAR(16);
DEFINE p_orno INTEGER;
DEFINE p_pono INTEGER;
DEFINE p_clot CHAR(16);
DEFINE COUNT INT;
LET count = 0;
FOREACH
SELECT t_orno, t_pono, t_clot
INTO p_orno, p_pono, p_clot
FROM ttdilc401100
WHERE t_orno = order AND t_pono = line
RETURN p_orno, p_pono, p_clot WITH RESUME;
LET count = count + 1;
END FOREACH;
END PROCEDURE
I would like to retreive fields t_cuno and t_item from table ttdsls041100. Table tdsls041100 will have 1 record to multiple records in ttdilc401100. They can be linked by t_orno and t_pono. Please could someone point me in the right direction. THANKS in advance.
CREATE PROCEDURE cusdef_ilc401 (order INTEGER, line INTEGER)
RETURNING INTEGER, INTEGER, CHAR(16);
DEFINE p_orno INTEGER;
DEFINE p_pono INTEGER;
DEFINE p_clot CHAR(16);
DEFINE COUNT INT;
LET count = 0;
FOREACH
SELECT t_orno, t_pono, t_clot
INTO p_orno, p_pono, p_clot
FROM ttdilc401100
WHERE t_orno = order AND t_pono = line
RETURN p_orno, p_pono, p_clot WITH RESUME;
LET count = count + 1;
END FOREACH;
END PROCEDURE
I would like to retreive fields t_cuno and t_item from table ttdsls041100. Table tdsls041100 will have 1 record to multiple records in ttdilc401100. They can be linked by t_orno and t_pono. Please could someone point me in the right direction. THANKS in advance.