CODEWITHSUNDEEP
oracle-databasenumberssequencerows
user2673474
user2673474

Reputation: 1071

Oracle SQL: adding the same sequence number to the rows of a query

I am sending to a downstream application events and I need in each event a sequence number, as a control number that I don't have gaps, I don't miss sending events. What I am trying to achieve is having a db sequence, to have assigned to the results of a query the same sequence number. I don't want to use a procedure, function, etc, only some SQL.

For example I need a query result like this:

col1row1  col2row1 seq1
col1row2  col2row2 seq1
col1row3  col2row3 seq1
col1row4  col2row4 seq1

The problems I found is that sequence have restrictions is queries or that I have different values for seq1.

I tried this:

SELECT t.*, ROWNUM, 
CASE ROWNUM
  WHEN 1 THEN MY_SEQ.NEXTVAL
  ELSE MY_SEQ.CURRVAL
END as event_seq
from my_table t

or 

SELECT t.*, ROWNUM, decode(rownum, 1, MY_SEQ.NEXTVAL, MY_SEQ.CURRVAL) from my_table t

and the sequence is defined:

CREATE SEQUENCE MY_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 
INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ;

Upvotes: 1

Views: 1617

Answers (1)

Bob
Bob

Reputation: 1055

If you are within the same session, you just just fill MY_SEQ.CURRVAL by making a call from dual with MY_SEQ.NEXTVAL.

select my_seq.nextval from dual;
select my_seq.currval, t.* from my_table t;

Upvotes: 1

Related Questions