OraFAQ Forum: Forms » sequence nos

Home » Developer & Programmer » Forms » sequence nos

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sequence nos [message #172426]

Tue, 16 May 2006 07:56

andirajkumar
Messages: 9
Registered: April 2006
Location: india

Junior Member

table name: sales_master
column name: invoice_no
the invoice_nos like s00001,s00002.... should be generated automatically whenever a new record is created. i got the following code.
declare
serialnos number(6);
begin
select NVL(MAX(substr(invoice_no,5,6)),0)+1 into serialnos
from sales_master;
:sales_master.invoice_no:='s'||LPAD(serialnos,3,0);
end;
i am getting compilation errors. guidance required

Report message to a moderator

Re: sequence nos [message #172437 is a reply to message #172426]

Tue, 16 May 2006 08:42

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

You need a 'to_number' before you can do '+1', and you need to do a 'to_char' before the 'lpad'.

David

Report message to a moderator

Re: sequence nos [message #172691 is a reply to message #172437]

Wed, 17 May 2006 12:01

vijaykkiran
Messages: 5
Registered: April 2006

Junior Member

hi

in your code there is a little bit error in using the substr function, the function will accept 3 parameters.

1st is the column name,
2nd is the starting position,
3rd is the lenght to read..

invoice_no
-----------
s00001
s00002
s00003

so, 's' is truncated then length is 5 so from position 2 to length of 4 should be use.. i.e. substr(invoice_no,2,4) as for your column values

so, make change this.

if the invoice_no column is varchar2 format then its is very happy,

just add a number to the existing values by this

declare
num number;
begin
select max(to_number(substr(invoice_no,2,4)))+1 into num from Inv_table;
:inv_no := 'S'||lpad(num,3,0);
end;

Report message to a moderator

Previous Topic:	Populating non-database item from record group ?
Next Topic:	can i do "continue"

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 09:48:02 CDT 2024