How do I write this SELECT statement?

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! Received on Fri Mar 05 2004 - 05:47:54 CST