Re: How do I write this SELECT statement?

Ubiquitous wrote:

> 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.

Thanks for the clarification. Still seems to me a simple change in architecture would fix the problem.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)