Re: Lost place in temporary table

[Steve Hall <srh@CONVOY.COM>]

Yeah.

You may have to keep track of where you were as you iterate thru.

And then change the WHERE clause to;
WHERE key_val > #key_val
ORDER BY [$order]


-----Original Message-----
From: Discussion of SQR, SQRIBE Technologies's database reporting
language [mailto:SQR-USERS@list.iex.net]On Behalf Of J Hart
Sent: Friday, June 11, 1999 11:40 AM
To: Multiple recipients of list SQR-USERS
Subject: Re: Lost place in temporary table


Are you doing any kind of COMMIT in your 2200-process-employee subroutine?
That will trigger the cursor to 'reset' and you will get an endless loop
like you describe.






Peggy McCall <peggy.mccall@PHARMA.NOVARTIS.COM> on 06/11/99 01:54:28 PM

Please respond to SQR-USERS@list.iex.net
To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:
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


----------------------------------------------------------------
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited.   If you received
this in error, please contact the sender and delete the material from any
computer.