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
Vinish Kapoor
Specify the nocache clause in the Create Sequence statement. Below is an example:
Swarup
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.
Vinish Kapoor
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:
Swarup
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
Iqbal
Wow..thank you.
Swarup
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;)
Vinish Kapoor
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.
Swarup
As suggested i have done it after that it shows error
If i put the alias name then it shows error
Vinish Kapoor
Because your column volunteer_id is alphanumeric (added 'A' as prefix). Change the query as below:
Swarup
Thanks Sir Now it works fine
Swarup
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
Vinish Kapoor
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'.
Swarup
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;
Vinish Kapoor
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:
Swarup
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
Vinish Kapoor
Change the form default value SQL again as follows:
Swarup
Thanx Sir,It works fine,
But when i reset the sequence in the form its showing IN01 instead of IN01001
Vinish Kapoor
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:
Swarup
When i run in SQL developer it shows 001
But in application it shows IN01
Vinish Kapoor
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.
Swarup
No Sir in Sql Developer the Result in IN01
Vinish Kapoor
You have to analyze the data. First, query the records from v_register. For example:
Swarup
There is no data the table empty
Vinish Kapoor
Try this:
Swarup
Now it works fine Thanx Sir, will be there any issue if the number increases
Vinish Kapoor
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.
Swarup
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;
Vinish Kapoor
I have given you many examples of the query. Now you will have to figure out.
Swarup
Ok sir sure thnx for your support