Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: An easier way to write this SQL

Re: An easier way to write this SQL

From: Chris Colclough <chris.colclough_at_jhuapl.edu.nospam>
Date: Thu, 29 Jul 1999 08:10:29 -0400
Message-ID: <37A044B5.19449454@jhuapl.edu.nospam>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US