Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: An easier way to write this SQL
Another option is to store an integer employee id. A view could be created
which selects employee_id as 'E'||lpad(emp_id,9.'0')
Juan Carlos wrote:
> 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 Thu Jul 29 1999 - 07:10:29 CDT