Mon, 01/21/2008 - 12:41am — Lepa

Here is a quick example on how the FLOOR function works to help you understand it better.

The following example returns the largest integer equal to or less than 18.6:

Generating a sequence number isn't that hard. All what you need to type is the following:

CREATE sequence my_seq start WITH 1;

Select from dual to increment your sequence number by one.

SELECT my_seq.NEXTVAL FROM dual;

What if you want to create a sequence that increments by 3?

SQL> CREATE sequence seq_3 start WITH 1 increment BY 3;

Sequence created.

SQL> SELECT seq_3.NEXTVAL FROM dual;

NEXTVAL

----------

1

SQL> SELECT seq_3.NEXTVAL FROM dual;

NEXTVAL

----------

4

SQL> SELECT seq_3.NEXTVAL FROM dual;

NEXTVAL

----------

7

Sequence created.

SQL> SELECT seq_3.NEXTVAL FROM dual;

NEXTVAL

----------

1

SQL> SELECT seq_3.NEXTVAL FROM dual;

NEXTVAL

----------

4

SQL> SELECT seq_3.NEXTVAL FROM dual;

NEXTVAL

----------

7

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.

SQL> CREATE sequence seq start WITH 1;

Sequence created.

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5

---------------

1

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5

---------------

2

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5

---------------

3

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5

---------------

4

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5

---------------

6

-- Notice how the number 5 is missing from the results as it is divisible by 5.

Sequence created.

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5

---------------

1

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5

---------------

2

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5

---------------

3

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5

---------------

4

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5

---------------

6

-- Notice how the number 5 is missing from the results as it is divisible by 5.

Here is a quick example on how the FLOOR function works to help you understand it better.

The following example returns the largest integer equal to or less than 18.6:

SELECT FLOOR(18.6) "Floor" FROM DUAL;

Floor

----------

18

Floor

----------

18

- Lepa's blog
- Login to post comments
- 98364 reads

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Title | Under | Posted on |
---|---|---|

Component interface Error: no rows exist for the specified keys | PeopleSoft Technical | 03/15/2019 - 3:54am |

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 |

## 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.