1

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

How to receive

' 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

2

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.

3

Re: Rownum

maxwait;
To use  not?

4

Re: Rownum

maxwait wrote:

not the same result.

RTFM

5

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;

6

Re: Rownum

maxwait wrote:

yes tried and
ORA-02287 Takes off

you Say lies/tupish.

7

Re: Rownum

Elic;
It is not eliminated)

8

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

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;

10

Re: Rownum

maxwait;
Add commit in a cycle.

11

Re: Rownum

maxwait;
Remove a nls-bomb smile))