Re: How do I write this SELECT statement?

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.

Don't write a SQL statement and stop the process. 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)