Stored Procedures with Multiple tables
Stored Procedures with Multiple tables
(OP)
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.
Talk To Other Members
RE: Stored Procedures with Multiple tables
Sounds like you're just having problem with a compound, i.e. more than one table, select. Provided I've interpreted what you want:
Given test tables:
create table ttdsls041100
(
t_cuno integer,
t_item char(20),
t_orno integer,
t_pono integer
);
create table ttdilc401100
(
t_orno integer,
t_pono integer
);
CREATE PROCEDURE some_values ()
RETURNING INTEGER, CHAR(20);
DEFINE x_cuno INTEGER;
DEFINE x_item CHAR(20);
FOREACH
select t_cuno, t_item INTO x_cuno, x_item
from ttdsls041100 a, ttdilc401100 b
where a.t_orno = b.t_orno and a.t_pono = b.t_pono
RETURN x_cuno, x_item WITH RESUME;
END FOREACH;
END PROCEDURE;