Re: How do I write this SELECT statement?

From: Ubiquitous <weberm_at_polaris.net>
Date: Fri, 5 Mar 2004 20:58:35 +0000 (UTC)
Message-ID: <c2aplr$5ca$1@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. Received on Fri Mar 05 2004 - 14:58:35 CST