Re: journal record reduction issue using SQL

On 12-Dec-2013 13:03 -0800, Stone, Joel wrote:

On 12-Dec-2013 11:52 -0800, CRPence wrote:

<<SNIP>> Another option is to just process the data sequentially
via the arrival access path [of a refreshed Output Member; see
above], and /massage/ the data to be compatible with the existing
query. <<SNIP>>

Massage the data in what way? I suggested adding a constant (such as
1,000,000,000,000) to every JOSEQN under 1,000,000 or so but you
correctly pointed out that to be flawed - for example if there were
TWO setbacks to deal with in out journal outfile.

How can I alter the JOSEQN to make sure JOSEQN = 1 & 2 & 3 presents
AFTER JOSEQN = 123,456 for example?

Add a new field?

As I had suggested, a constant is a poor choice. The data for all reset sequence numbers best would be incremented with the highest previously chosen value [in a sense, calculated, to differentiate from a constant], established from before each reset. Not easy to explain in words, so for example:

Given the following table of sequences (heading Seq) whereby the JOSEQN has been reset twice [and the ellipses imply the sequence of consecutive counting numbers continues its by-one increment], the second column (heading Add) shows what would be added, and the third column shows the final result (heading NewSeq) as the effect of the changed JOSEQN:

Seq Add NewSeq
7455 0 7455
7456 0 7456
1 7456 7457
2 7456 7458
... 7456 (each...+7456)
3009 7456 10465
3010 7456 10466
1 10466 10467
2 10466 10468
... 10466 (each...+10466)
3008 10466 13474
3009 10466 13475
3010 10466 13476
3011 10466 13477
... 10466 (each...+10466)
7454 10466 17920
7455 10466 17921
7456 10466 17922
7457 10466 17923
... 10466 (each...+10466)
11222 10466 21688

That algorithm would be capable of supporting over five sequence number resets even while at the boundary of the "Maximum sequence number for journal entries"; i.e. per the approximate number of times ~18.5 goes into 100.