Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedures with Multiple tables

Status
Not open for further replies.

reecem

Programmer
Mar 11, 2002
15
BE
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.
 
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;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top