weberm_at_polaris.net (Ubiquitous) wrote in message news:<c2aplr$5ca$1_at_news.utelfla.com>...
> Daniel Morgan <damorgan_at_x.washington.edu> wrote:
> :> : Ubiquitous wrote:
>
> :> Perhaps I didn't explain it clearly. The file sequence number is assigned
> :> to a set of transactions stored on the table and then used to generate
> :> a similar two-character identifier on a load file (the file is used to load
> :> an IMS database on a mainframe and cannot be changed).
> :>
> :> We only have problems if a set of transactions gets held up and is resolved
> :> at the same time as another with the same last digit, causing both sets
> :> to be lumped together. So, for example, if a problem with file sequence
> :> number 131 is resolved on the same day 141 is received and processed, they
> :> get written to the load file together with a work period "81". Granted, this
> :> only happens if it takes ten days to resolve, but it's a nuisence when it
> :> occurrs.
> :
> : Thanks for the clarification. Still seems to me a simple change in
> : architecture would fix the problem.
>
> Oh, I agree, but we're going to be migrating the mainframe processing "soon"
> (whenever that may be) and changing the database there is out of the
> question.
>
> I did, however, think I found a solution:
>
> SELECT SUBSTR(proc_file_seq_nbr,LENGTH(proc_file_seq_nbr)) "LAST DIGIT",
> COUNT(DISTINCT proc_file_seq_nbr)
> FROM credit_card_records_received
> WHERE processor_source = 'DFS-ALIS'
> AND reconciliation_flag = 'R'
> AND doi_receipt_number IS NULL
> GROUP BY SUBSTR(proc_file_seq_nbr, LENGTH(proc_file_seq_nbr))
> HAVING COUNT(DISTINCT proc_file_seq_nbr) > 1;
>
> I need to test it further but I think this will tell me if two sets with
> the same rightmost digit are eligible for processing.
If your data sets are consecutively numbered then surely you can just
count the number of sets to process. If you have >10 then you're going
to get a repeat in the least significant digit.
Received on Tue Mar 09 2004 - 07:56:42 CST
