Create and store a Sequence in a Table

We have a functionality where we need to create a sequence from 0 with increment of 1 and going up to a maximum value (count of a table).

We are using a dynamic variable 'maxi' that take the max count of a table and stores this max value.

Next we are creating a SEQUENCE between 0 and maximum value that we obtained in the previous step.

Query we are using is - CREATE SEQUENCE SERIES increment by 1 minvalue 0 maxvalue <%=maxi%> start with 0

The problem is: We need to store this sequence in a table and then use that table to do a CROSS JOIN with another query. We are not able to develop a correct syntax to store this sequence in a table.

We would need your help in developing the correct queries to:

Create a table that stores a sequence from 0 to a max value (say 100), and
Create a correct CROSS JOIN reference with another query

Page 1 / 1

Hello,

You can try just to include IDENTITY(1,1)column when creating the table.

It should increment by 1 with each inserted row, so it would make same purpose and would be added automatically.

Could you check code below? You can insert multiple rows by just executing it few times.

Code to test:

CREATE TABLE SomeTable(

ID IDENTITY(1,1),

lname VARCHAR(25),

fname VARCHAR(25),

store_membership_card INTEGER

);

INSERT INTO SomeTable (lname, fname, store_membership_card )

VALUES ('Gupta', 'Saleem', 475987)

SELECT * FROM SomeTable

DROP TABLE IF EXISTS SomeTable

Best Regards,

Mateusz Dudek

Hello,

You can try just to include IDENTITY(1,1)column when creating the table.

It should increment by 1 with each inserted row, so it would make same purpose and would be added automatically.

Could you check code below? You can insert multiple rows by just executing it few times.

Code to test:

CREATE TABLE SomeTable(

ID IDENTITY(1,1),

lname VARCHAR(25),

fname VARCHAR(25),

store_membership_card INTEGER

);

INSERT INTO SomeTable (lname, fname, store_membership_card )

VALUES ('Gupta', 'Saleem', 475987)

SELECT * FROM SomeTable

DROP TABLE IF EXISTS SomeTable

Best Regards,

Mateusz Dudek

Thank you. I will try this

Hello,

I've missed one thing, in your case use Identity(1,1,1) the last number tells about cache, when leaved the default value is 250000, and it may cause some troubles.

Best Regards,

Mateusz Dudek

Thank you. I will try this

Can we limit this last number based on count of some other table?

So the scenario is that we have a table with30 entries and we need to create this dynamic table with those 30 rows.

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN