Peggy,
If you are creating the temporary table from within the 2200-process-employee
procedure you are running into an implicit commit. The create table statement
automatically issues a commit this resets your main select statement and pretty
much causes it to start over. Try creating both temporary tables first, outside
of the select loop.
Hope that works for you.
Kimberly
(Embedded
image moved Peggy McCall <peggy.mccall@PHARMA.NOVARTIS.COM>
to file: 06/11/99 01:54 PM
pic21133.pcx)
Please respond to SQR-USERS@list.iex.net
To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc: (bcc: Kimberly Lawrence/MIS/Circuit City)
Subject: Lost place in temporary table
I'm creating a temporary table from a flat file, then reading it back in a
user-defined order using dynamic SQL:
BEGIN-SELECT
x1.EMPLID
let $emplid = &x1.EMPLID
x1.AS_OF_DATE
let $as-of-date = &x1.AS_OF_DATE
do 2200-process-employee
FROM [$select-table-name] x1
ORDER BY [$sort-order]
END-SELECT
In the 2200-process-employee procedure I create a second temporary table using a
different table name.
My problem is that the first select keeps returning the first row after
performing the 2200-process-employee procedure,
thus sending my program into a endless loop.
I've stripped down the performed procedure to see what starts the problem, and
I've found that as long as I don't
access the 2nd temporary table AT ALL the first select works perfectly. If I
even do so much as to DROP the 2nd table,
the first select goes back to returning only the first row.
We're running: SQR/4.2.3/PC/Windows NT 4.0/Oracle 7.3.2.2
Any help would be MUCH appreciated - we've tried everything we can think of.
Peggy McCall
Attachment:
pic21133.pcx
Description: Binary data