Michael,
You might consider using a TIMESTAMP WITH DEFAULT CURRENT_TIMESTAMP instead of an INTEGER column. You could then delete and order based on the timestamp instead of the integer/RRN. You don't have to futz with your insert statement or use CHGPF.
Paul Morgan
Principal Programmer Analyst
IT Supply Chain/Replenishment
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Koester, Michael
Sent: Monday, September 12, 2011 11:48 AM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN
Thanks Michael and Robert and Joe and Paul and Kurt.
To Joe's question about what happens downstream, the table contains commands that are is used by another program where those commands are sent in sequence to another platform (telephone switch equipment). A week or so ago, I saw one instance (from over 800 orders processed) of an unexplained out-of-sequence processing. That was one where delete was not involved -- the mischief was probably due to SQL's creative ordering. Sure enough, there is no ORDER BY rrn(a). I guess I've been pretty lucky, but clearly I need to get this fixed.
Paul, the rows get deleted with an "SQL DELETE ... where rrn(a) between :x and :y ...". The single column is not unique, and even if it was, does not contain anything pertinent to its processing sequence.
Based on the statement...
The order in which rows are inserted does not guarantee the order in which they will be retrieved.
... I'm thinking I'd better modify my design to include an integer row# column.
I was looking at the RowID data type, but QTEMP will have no part of that (at v6.1, anyway).
If I did not go the added column route, would I be safe in changing the file to REUSEDLT(*NO), and adding ORDER BY to the sql retrieving the rows to process?
-- Michael K
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Koester, Michael
Sent: Monday, September 12, 2011 10:31 AM
To: RPG400-L@xxxxxxxxxxxx
Subject: SQL Insert Row as last RRN
I have a very simple 1-column workfile created on the fly with embedded SQL. As I add rows to the table, some situations require that some rows need to be deleted, which works just fine. But the next row added then gets stuffed into the workfile in the position (relative-record-number-wise) of the first row vacated by the delete. I didn't anticipate this when I designed my process, so I didn't build in a sequence number column. All the access to this workfile is through SQL - no RLA in this program.
Is there a way to specify on the INSERT statement that the new row is to have an RRN greater than that of the greatest RRN already written? If not, is there a simple way to re-sequence (compress) out the deleted rows after my DELETE statement, so that the result has contiguous RRNs (thereby leaving no gaps, and a subsequent INSERT would have to go to the "end" of the workfile)?
Thanks.
Michael Koester
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
________________________________
Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.
midrange.com
