Re: Using Stored Procedures within SQR with temp_tables.

[johnk@SUN4.MITI.COM]

     Since you have two temporary tables that are accessed by the same
     stored procedure, you are forced to create the temp tables under the
     same connection (-C1). However, a nested select statement is not
     allowed to use the same connection number as a parent query which is
     why you get the error. I would recommend creating only one temp table
     that manages to join the data from the original two tables and thus
     allows the nested query to be eliminated or allows both queries to
     access the same table.

     For example, if the temp tables originally looked like this:


        Table1            Table2
        ------            ------
        col1              col1
        col2              col3
                          col4


     Create the one table as:

        Table1
        ------
        col1
        col2
        col3
        col4


     Since there was a one to many relationship when having two tables, the
     data might have looked like this:

        Table 1           Table2
        -------           ------
        1 2               1 3 4
                          1 3 5

        2 2               2 3 4
                          2 3 5
                          2 3 6

     The data would now look like this:

        Table1
        ------
        1 2 3 4
        1 2 3 5
        2 2 3 4
        2 2 3 5
        2 2 3 6

     Regards,

     John L. Kellogg
     MITI Technical Support Manager


______________________________ Reply Separator _________________________________
Subject: Using Stored Procedures within SQR with temp_tables.
Author:  usa.net!SQR-USERS@netcomsv.netcom.com at Internet
Date:    12/04/96 10:26 AM


I have a question about using stored procs within SQR with temp_tables.  Here
is my problem.

I have one stored proc that manipulates several temp_ tables.

When the stored proc finishes I have 2 temp tables.

I then want to step through the first temp_table and while I am stepping
through that table
I want to call a SQR procedure that will then step through the second table.
I have a one to many relationship between the two tables.

in code it looks something like this..

begin-sql -C1
create table ##table1
create table ##table2
end-sql

execute -C1
   @#rc = my_proc

I then want to enter a begin-select statment

begin-select -C1

company &company

  do process_table2

from ##table1
end-select


Begin-Procedure
begin-select -C1

more_data &more_data

from ##table2
where company=&company
end-select
End-Procedure

what happens is I get an error message back saying that:

requested -C database connection is already active.


I understand what the problem is, I am trying to enter two begin-selects with
the same connection number (C1).

I would like to know if there is a way I can make this work !

I tried creating the two tables with different connections, but there is know
way to tell the stored proc that I want to use two connections.
So the stored proc call bombs out saying that it can't find one of the tables.

I am running SQR 3.0.8 under SYBASE. 10.0.2.

Any help would be greatly appreciated.

Thanks,
John Palmieri