Re: An easier way to write this SQL

There's other ways you could construct the SELECT statement, but they're just as messy. You *could* create a function that contains all the messy stuff, and then simply use the function in the SELECT statement. That would clean it up a little.

I don't suppose you can't use a sequence because *someone* says there can be no skips in the employee numbers, huh? :=)

Regards,

--
David C. Sisk
Need tech info on Oracle? Visit The Unofficial ORACLE on NT site at http://www.ipass.net/~davesisk/oont.htm Like original music? Listen to song samples and buy a CD at http://www.mp3.com/disparityofcult

Juan Carlos wrote in message <7no6m7$a31$1_at_bgtnsc03.worldnet.att.net>...
>We have a table containing an Employee ID in the format 'E00000001'. It is
>monotonically incremented. I want to write a SQL statement to get the next
>available number .e.g 'E00000002'. We can't use a sequence for reasons I
>won't bore you with. The following works, but is there a more elegant way
>to write it?
>
>SELECT 'E' || LPAD(MAX(TO_NUMBER(SUBSTR(EXTERNAL_ID,2,9))+1),9,'0')
>FROM EXTID
>
>
>
Received on Wed Jul 28 1999 - 22:16:56 CDT