Re: How do I write this SELECT statement?

Daniel Morgan <damorgan_at_x.washington.edu> wrote: : Ubiquitous wrote:

:> I have a table containing transactions which are processed on a daily
:> basis and written to a flat file which is used to load into a mainframe
:> application. One of the fields contains a file sequence number which
:> is used to derive a work period by concatonating the last (rightmost)
:> digit with an "8". This works fine and dandy except that if there
:> is a delay in processing due to data errors, etc, we end up combining
:> two file sequnmce numbers with the same rightmost digits into one
:> work period, which causes all sorts of grief.
:>
:> I am looking at adding a SELECT statement that would determine if
:> there are more than one file sequence number with the same rightmost
:> digit and stop the job, but am not sure on how to set up the query.
:> I suspect I need to write some sort of embedded self-SELECT but am
:> not sure how to define it.
:>
:> This is what I've gotten so far (in pseudo English-PL/SQL):
:>
:> SELECT COUNT(*) into v_fsn_count
:> FROM CCRR
:> WHERE record is unprocessed
:> AND ? -- rows with same rightmost digit in fsn
:>
:> IF v_fsn_count > 1 THEN
:> Return_error
:> ELSE
:> Process_as_normal
:> END IOF;
:>
:>
:> So a CCRR table containing file sequence numbers 10 and 20 would
:> perform Return_error but file sequnce numbers 10 and 11 would process
:> normally.
:>
:> Thank you in advance!

: I'm sure I don't understand why you have an architecture that makes
: this possible. If it is possible for two different entities to have
: the same file sequence number someone failed data architecture 101.

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. Received on Fri Mar 05 2004 - 12:19:33 CST