CODEWITHSUNDEEP
sqloracle-database
Mr.Maze
Mr.Maze

Reputation: 125

Sequence next value in SELECT statement

I would like to use the same sequence number across multiple select statements in SQL form to eventually use it as a PK insert in a table and tie multiple records together.

So far, I can only select the NEXTVAL from dual:

SELECT TEST_SEQ.NEXTVAL AS SEQ FROM DUAL;

However, when I include the sequence into a multiple column select, I get sequence not allowed here error. 

SELECT col1, co2, col3, (select TEST_SEQ.NEXTVAL) SEQ
FROM table;

Any help is greatly appreciated.

Upvotes: 12

Views: 142828

Answers (4)

Mugeesh Husain
Mugeesh Husain

Reputation: 428

select SEQ_tablename.NEXTVAL from DUAL;

Upvotes: 1

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4233

In tsql use Next Value for dbo.seqYourSequence to retrieve a value

Upvotes: 1

Mohammed Osman
Mohammed Osman

Reputation: 4226

If you want to select the next value from sequence object, you can use this SQL statement.

SELECT NEXT VALUE FOR [dbo].[seq_Vehicles] AS NextVehicleId

If you want to select multiple next values from SQL Sequence, you have to loop calling the above SQL statement and save the "next value" got in a storage.

You can loop using (while loop) or by (cursor).

Upvotes: 6

user330315
user330315

Reputation:

Don't use a sub-select:

SELECT col1, co2, col3, TEST_SEQ.NEXTVAL as SEQ
FROM table;

Upvotes: 9

Related Questions