#### Topic: Rownum

Good afternoon!
Question.
There is a request

``````BEGIN
for data_r1 IN (SELECT ddate from tabl1 where ddate between ' 9/1/2017 ' and ' 9/11/2017 ')
LOOP
BEGIN
INSERT INTO Table3
SELECT data_r1.ddate
, Account
, col3
, col4
, row_number () over (order by Account asc, data_r1.ddate+1 asc)
FROM table2;
END;
END;``````

As a result I receive such result

``````' 9/1/2017 ', ' 123 ', 1
' 9/1/2017 ', ' 234 ', 1
' 9/1/2017 ', ' 456 ', 1
' 9/2/2017 ', ' 123 ', 2
' 9/2/2017 ', ' 234 ', 2
' 9/2/2017 ', ' 456 ', 2
............................
' 9/11/2017 ', ' 123 ', 11
' 9/11/2017 ', ' 234 ', 11
' 9/11/2017 ', ' 456 ', 11``````

``````' 9/1/2017 ', ' 123 ', 1
' 9/1/2017 ', ' 234 ', 2
' 9/1/2017 ', ' 456 ', 3
' 9/2/2017 ', ' 123 ', 4
' 9/2/2017 ', ' 234 ', 5
' 9/2/2017 ', ' 456 ', 6
............................
' 9/11/2017 ', ' 123 ', 31
' 9/11/2017 ', ' 234 ', 32
' 9/11/2017 ', ' 456 ', 33``````

Ordinal value of a line.
Tried , ORA-02287

#### Re: Rownum

Such still the variant was

``````declare
x number: = 0;
BEGIN
for data_r1 IN (SELECT ddate from tabl1 where ddate between ' 9/1/2017 ' and ' 9/11/2017 ')
LOOP
BEGIN
INSERT INTO Table3
SELECT data_r1.ddate
, Account
, col3
, col4
, x - row_number () over (order by Account asc, data_r1.ddate+1 asc)
FROM table2;
END;
x: = x+1;
END;``````

Not the same result.

maxwait;
To use  not?

#### Re: Rownum

maxwait wrote:

not the same result.

#### Re: Rownum

saxarock;
Yes tried and
ORA-02287 Takes off

``````BEGIN
for data_r1 IN (SELECT ddate from tabl1 where ddate between ' 9/1/2017 ' and ' 9/11/2017 ')
LOOP
BEGIN
INSERT INTO Table3
SELECT data_r1.ddate
, Account
, col3
, col4
, .nexval
FROM table2;
END;
END;``````

#### Re: Rownum

maxwait wrote:

yes tried and
ORA-02287 Takes off

you Say lies/tupish.

#### Re: Rownum

Elic;
It is not eliminated)

#### Re: Rownum

maxwait;

``````with t as (select ' 9/1/2017 ' ddate from dual union all
select ' 9/2/2017 ' ddate from dual union all
select ' 9/11/2017 ' ddate from dual)
, table2 as (select ' 123 ' id from dual union all
select ' 234 ' id from dual union all
select ' 456 ' id from dual)
select t.ddate, t2.id, row_number () over (order by ddate, t2.id) rn
from table2 t2, t``````
``````DDATE ID RN
01.09.2017 123 1
01.09.2017 234 2
01.09.2017 456 3
02.09.2017 123 4
02.09.2017 234 5
02.09.2017 456 6
11.09.2017 123 7
11.09.2017 234 8
11.09.2017 456 9``````

#### Re: Rownum

;
Aha, I repent, corrected.

``````BEGIN
for data_r1 IN (SELECT ddate from tabl1 where ddate between ' 9/1/2017 ' and ' 9/11/2017 ')
LOOP
BEGIN
INSERT INTO Table3
SELECT data_r1.ddate
, Account
, col3
, col4
, row_number () over (order by Account asc, data_r1.ddate+1 asc)
FROM table2;
END;
END LOOP;
END;``````

maxwait;