×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Stored Procedures with Multiple tables

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.

RE: Stored Procedures with Multiple tables

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;

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close