Hi Sir,
I am creating alphanumeric sequence number in the id field by using this
create sequence VID1 start with 1 increment by 1 ——in Sql Commands
(IF :P2_VOLUNTEER_ID IS NULL THEN
SELECT ‘A’ || VID1.NEXTVAL INTO :P2_VOLUNTEER_ID FROM DUAL;
END IF;)—-Process in Application
But the issues was that when i am creating something continuously its working fine if i leave for sometime or logout and login after some time or next day then the sequence number start from different number.
Eg: continues A41,A42,A42
After logout and login or sometime A81,A82
But i need continues where i left from the next number onwards
Specify the nocache clause in the Create Sequence statement. Below is an example:
Thanx Sir, it works fine,
When i click on the Registration form a id is generating automatically but i need until i didn’t save that id ,same id should reflect in the registration form once i click on the create button then a new id should generate in the registration form when i click on it.
Eg:A10 should come until i create the record whenever i open the registration form
once i entered the record for A10 then A11 should come.
Use before insert database trigger to assign the sequence value and on form show the ID value using the SQL query as following:
It will just show the value on the form, overwrite its value using the before insert trigger as following:
Hi Sir, Step 1: create or replace Trigger V_REGISTER_T2 Before Insert On V_REGISTER For Each Row Begin :new.ID := ‘A’||VID1.nextval; End; in Sql Command Step 2: and on form show the ID value using the SQL query as following:
select 'A'||(max(nvl(ID, 0))+1) from my_table i didn't get this point
in form where i will assign this query
Wow..thank you.
Hi sir
I want to generate Id as IN01001 that mean IN01 should be prefix and the number should start 001
select ‘IN01’||(max(nvl(substr(VOLUNTEER_ID, 2), 0))+1) as VOLUNTEER_ID from V_REGISTER;
it is not working
I think you should also use the lpad() function to fill ‘0’ at the left, for example:
And for the trigger:
But when the length increases of the digits, it will be like IN01012.
Or maybe you need to just add the 00 to the string, for example: ‘IN0100’.
I am getting following error after using this
create or replace Trigger V_REGISTER_T2 Before
Insert On V_REGISTER
For Each Row
Begin
:new.VOLUNTEER_ID := ‘IN0100’||lpad(VID1.nextval, 3, ‘0’);
End;
———————————-
select ‘IN0100’||lpad((max(nvl(substr(VOLUNTEER_ID, 2), 0))+1), 3, ‘0’)
as VOLUNTEER_ID from V_REGISTER;
The command is correct. I have already tested.
Previously, you were adding the ‘A’ only, so I suggested the substr function. Now one more alphabetic character added because you are now want to use ‘IN’. Change the SQL as follows:
I am getting this error
i have created the following
My sequence (Create Sequence VID1 start with 1 increment by 1 nocache order😉
My trigger(create or replace Trigger V_REGISTER_T2 Before
Insert On V_REGISTER
For Each Row
Begin
:new.VOLUNTEER_ID := ‘A’||VID1.nextval;
End;)
You will have to specify that query for the Default value of SQL type, it is not for the Select List SQL query. Then it will work.
As suggested i have done it after that it shows error
If i put the alias name then it shows error
Because your column volunteer_id is alphanumeric (added ‘A’ as prefix). Change the query as below:
Thanks Sir Now it works fine
Thnx Sir it works,
But in report it shows IN01001 but when i click on the form it should show IN01002 but its showing IN01100
Change the form default value SQL again as follows:
Thanx Sir,It works fine,
But when i reset the sequence in the form its showing IN01 instead of IN01001
Try to run the SQL query in SQL developer and see why it is returning only IN01. Meaning this part (lpad((max(nvl(substr(VOLUNTEER_ID, 5), 0))+1), 3, ‘0’)) is returning the null value.
Because if I run only the following SQL, it returns the desired result:
When i run in SQL developer it shows 001
But in application it shows IN01
Run this query and then check what it is returning:
If it is returning the desired result, then it means you are doing something wrong in Apex page.
No Sir in Sql Developer the Result in IN01
You have to analyze the data. First, query the records from v_register. For example:
There is no data the table empty
Try this:
Now it works fine Thanx Sir, will be there any issue if the number increases
The left padding of ‘0’ will be removed when the length will be 3 or greater than 3. Then it will show the id as IN01101 or IN011001.
after IN01099 it come IN01100,IN01101 but after IN01999 it again comes IN01100
what to edit in this sql
select ‘IN01’||lpad(nvl((max(nvl(substr(VOLUNTEER_ID, 5), 0))+1),1), 3, ‘0’)
as VOLUNTEER_ID from V_REGISTER;
I have given you many examples of the query. Now you will have to figure out.
Ok sir sure thnx for your support