Hi!
I have a table with an id column which I would like to update with values 1 to n. Value n is to be defined each time the script is run.
I´m using the MOD function, but it´s not working as desired. If I set NUMBER_SCRIPTS=3 most of the rows are assigned ID=3. Should be assigned in the order of size by bytes (see cursor) as 1,2,3,1,2,3,1,2,3,etc. and if I set define NUMBER_SCRIPTS to 5, for example, the rows in descending order by size should be assigned 1,2,3,4,5,1,2,3,4,5,1,2,etc.
Please let me know if the objective is not clear and I´ll explain with more "real" examples.
Thanks in advance for your help. I have found this forum to be most valuable in the past and I am most grateful for it.
The relevant part of the script is as follows:
DEFINE NUMBER_SCRIPTS=&1
DECLARE
N_SCRIPT INTEGER:=&&NUMBER_SCRIPTS;
CURSOR CONTROL IS
SELECT SEGMENT_NAME, SEGMENT_TYPE, ROWNUM
FROM REPLICA_CONTROL_TABLE
WHERE SEGMENT_TYPE ='MVIEW'
AND ACCION='REFRESH'
ORDER BY BYTES DESC;
FOR R_CONTROL IN CONTROL LOOP
UPDATE TABLE SET ID=MOD(ROWNUM, N_SCRIPT)+1;
commit;
END LOOP;
Regards,
Teri.
I have a table with an id column which I would like to update with values 1 to n. Value n is to be defined each time the script is run.
I´m using the MOD function, but it´s not working as desired. If I set NUMBER_SCRIPTS=3 most of the rows are assigned ID=3. Should be assigned in the order of size by bytes (see cursor) as 1,2,3,1,2,3,1,2,3,etc. and if I set define NUMBER_SCRIPTS to 5, for example, the rows in descending order by size should be assigned 1,2,3,4,5,1,2,3,4,5,1,2,etc.
Please let me know if the objective is not clear and I´ll explain with more "real" examples.
Thanks in advance for your help. I have found this forum to be most valuable in the past and I am most grateful for it.
The relevant part of the script is as follows:
DEFINE NUMBER_SCRIPTS=&1
DECLARE
N_SCRIPT INTEGER:=&&NUMBER_SCRIPTS;
CURSOR CONTROL IS
SELECT SEGMENT_NAME, SEGMENT_TYPE, ROWNUM
FROM REPLICA_CONTROL_TABLE
WHERE SEGMENT_TYPE ='MVIEW'
AND ACCION='REFRESH'
ORDER BY BYTES DESC;
FOR R_CONTROL IN CONTROL LOOP
UPDATE TABLE SET ID=MOD(ROWNUM, N_SCRIPT)+1;
commit;
END LOOP;
Regards,
Teri.