RE: Using SQL to "mix" the records in a file

Alan,

Create an artificial key using the SQL RRN() function. Set a new key field to ( RRN(table) * 2 - 1 ) for the A records and
( RRN(table) * 2 - (count of A records) ) for the B records then reorder on the new key field.

If you have to sequence based on the timestamp and there is room in between the A timestamp records then set the B timestamps to a corresponding A timestamp plus a small increment (Second? Microsecond?). Use the RRN() function with an offset to link a B record to a corresponding A record RRN() you want it to follow.

Paul Morgan

Principal Programmer Analyst
IS Supply Chain/Replenishment

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Monday, August 09, 2010 2:30 PM
To: Midrange Systems Technical Discussion
Subject: Re: Using SQL to "mix" the records in a file


As soon as I pressed Enter, I wasn't too sure that I had explained myself
So here is my second attempt
The file contains 1,000 records, by timestamp, but as it happens, the first
500 are record types A and the second 500 are record types B (by timestamp)

I need to change the timestamp so that the 2 types are mingled
A
B
A
B
A
B

by timestamp

Hopefully that makes it a little clearer

Again, thanks in advance