Substituir AUTONUMBER(ROWNO()) por Peek

[maxwellf]

Olá,

Tenho um script que usa o AUTONUMBER(ROWNO()) para completar uma PK.

if(even(AUTONUMBER(ROWNO(), %pk_emp_est_func_data)), '2', '1')

E depois

%pk_emp_est_func_data &'|'& AUTONUMBER(ROWNO(), %pk_emp_est_func_data)

É possível substituir por PEEK? Como ficaria?

Visto que tenho 680.000 de dados e está demorando 2hrs para carregar.

[vinieme12]

Please post some sample data and provide some context as to the usage and purpose of this field 

[maxwellf]

Tabela original:

%pk_emp_est_func_data	entrada
001|1|1567|44635	05:20
001|1|1567|44635	07:00
001|1|1567|44635	14:30
001|1|1567|44635	18:20
001|1|1568|44635	05:20
001|1|1568|44635	07:00
001|1|1568|44635	14:30
001|1|1568|44635	18:20
001|1|1568|44635	18:40
001|1|1568|44635	19:20
001|1|1568|44635	20:21
001|1|1568|44635	22:10

if(even(AUTONUMBER(ROWNO(), %pk_emp_est_func_data)), '2', '1')

Esse código vai gerar esse id_entr_said

%pk_emp_est_func_data	entrada	id_entr_said
001|1|1567|44635	05:20	1
001|1|1567|44635	07:00	2
001|1|1567|44635	14:30	1
001|1|1567|44635	18:20	2
001|1|1568|44635	05:20	1
001|1|1568|44635	07:00	2
001|1|1568|44635	14:30	1
001|1|1568|44635	18:20	2
001|1|1568|44635	18:40	1
001|1|1568|44635	19:20	2
001|1|1568|44635	20:21	1
001|1|1568|44635	22:10	2

%pk_emp_est_func_data &'|'& AUTONUMBER(ROWNO(), %pk_emp_est_func_data)

Esse código gera o ultimo %pk_emp_est_func_data_row

%pk_emp_est_func_data	entrada	id_entr_said	%pk_emp_est_func_data_row
001|1|1567|44635	05:20	1	001|1|1567|44635|1
001|1|1567|44635	07:00	2	001|1|1567|44635|2
001|1|1567|44635	14:30	1	001|1|1567|44635|3
001|1|1567|44635	18:20	2	001|1|1567|44635|4
001|1|1568|44635	05:20	1	001|1|1568|44635|1
001|1|1568|44635	07:00	2	001|1|1568|44635|2
001|1|1568|44635	14:30	1	001|1|1568|44635|3
001|1|1568|44635	18:20	2	001|1|1568|44635|4
001|1|1568|44635	18:40	1	001|1|1568|44635|5
001|1|1568|44635	19:20	2	001|1|1568|44635|6
001|1|1568|44635	20:21	1	001|1|1568|44635|7
001|1|1568|44635	22:10	2	001|1|1568|44635|8

[marcus_sommer · Accepted solution]

Autonumber() with rowno() which should create a dimension depending counter is slow - even with small data-sets like the mentioned 680 k of records. AFAIK it couldn't really optimized. Therefore you may switch to interrecord-functions to solve your task with a much better run-time. This may look like:

load *, if(Field = previous(Field), rangesum(1, peek('Counter')), 1) as Counter
resident Source order by Field, Date desc;

- Marcus