Stored Procedures with Multiple tables

[reecem]

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.

 

[olded]

reecem:

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;