Generating a sequence number isn't that hard. All what you need to type is the following:
Select from dual to increment your sequence number by one.
What if you want to create a sequence that increments by 3?
Now, let's make it a little harder. what if you want to create a sequence # that is not divisible by 5?! The most straight forward way of doing this is to use the FLOOR function while selecting from dual.
Title | Under | Posted on |
---|---|---|
ADD 24 months starting from current month.(peoplesoft) | PeopleSoft Functional | 07/29/2018 - 8:44pm |
TRC values dropdown | PeopleSoft Technical | 04/04/2018 - 12:54am |
how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode | PeopleSoft Technical | 09/11/2017 - 4:49am |
How to add custom process in Paycycle PeopleSoft Accounts payable. | PeopleSoft Technical | 06/07/2017 - 4:39am |
Comments
Can any one tell me how to generate number sequence by just a select query with dual table and without creating a Sequence??????
can anybody tell me how to generate sequence in netezza server..??
Yes, and with a standard sql that works on any server.
The method is to use a self-join with grouping to generate the sequence number.
What this does is to join each row to itself and all those within the same sub-set, in this case ‘userid’, that were inserted before it and then count them. So the first entry for each user is only joined to itself = 1, the second is joined to itself and the first = 2, third to itself and the first two = 3, etc.
To reverse the sequence simply reverse the selection
This example uses the userid as the sub-set definition, but it could be any field you want.
You can find a fuller discussion of this solution with examples of its' use at mu blog davidsoussan.co.uk
i have a column containing 4 digit numbers ...such as 8841,8853,8823,9951,8841..etc column contain repeat values too ...
i need to convert them into 7 digit...and want to assign them 3 more digits
eg
8841 gets converted into 8841001 next
8841 gets converted into 8841002 next
8841 gets converted into 8841003 next
8853 gets converted into 8853001 next
9951 gets converted into 9951001 next
9951 gets converted into 9951002 and so on...
kindly help....i am a noob in sql...plz
Nice post Lepa......................
Regards,
Paritosh
You could simplify the algorithm :
SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL)/5) not_divisible_5 FROM dual;
it is more intuitive as the 'not required' factor becomes the denominator
select level
from dual
connect by level <= 10 ;
Anyone can help me how to add the sequence in the 1 query which the query have select and join other table, but for the sequent, it was the only one table. How to join it if there is no synonym table for sequent with the main table selected.