Auto Next Number 1

[mohiz]

Here is the code i used to get the desire result. The only problem is if there is null value then it does not generate the next number.

declare
matter_no number(2);
begin
select max(to_number(substr(matter_code,1,2))) + 1 into :matter.matter_code from matter where
client_code=:matter.client_code;
if :matter_code > 1 and :matter_code <= 10 then
:matter.matter_code:='0'||matter_no;
elsif client_no >=10 and client_no <=99 then
:client.client_code:='00'||client_no;
elsif client_no >=100 and client_no <=999 then
:client.client_code:='0'||client_no;
else :matter.matter_code:=''||matter_no;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
message('No record exist');
end;

any advice or correction in the code will be appreciated.

 

[sem]

Have you ever heard about Oracle sequences? About format masks and lpad function? Why do you need this ''||matter_no instead of matter_no? And why do you use both fully qualified names with short ones? Hod do you plan to process the situation when substr(matter_code,1,2) is not a number at all?
And finally it's not clear what you're trying to achieve.

IMHO this code is terrible and null values (BTW where?) is perhaps its least problem.




Regards, Dima

http://seminihin.narod.ru

 

[mohiz]

Sem I will appreciate if can give me some exampel

This is what i want to achive Matter No : 000033
next no 000034 should be generated automatically. If value in the column is null then 00001 should be generated.

thank you

 

[sem]

So I can not understand the relation between matter_no, matter_code, client_no and client_code. Does any exist? If the only problem is with adding 1 to null, then use nvl function.

Regards, Dima

http://seminihin.narod.ru