Looukp reading 1 row at a time

[jseclen]

Hi Forum,

Im using lookup to use an oracle sequencer to obtain a generate number,

odbc stage ------> lookup ----> odbc fields + oracle nextval
oracle stage ----> stage

The problem is when the retrieve the nextval this is the same value por a group of records, 5 records or 10 records, is different from each execution, i need the lookup read 1 record and obtain a next value from the sequencer.

I set the oracle stage as Sparse lookup, but the problem is the same,

Is there some environmental variable to force to lookup to read 1 record at a time to and read the oracle sequncer to obtain the value???

[chulett]

The problem is when the retrieve the nextval this is the same value por a group of records, 5 records or 10 records, is different from each execution

Not really sure what this means. If you mean your first call to 'nextval' gets you 5 (for example) and the second is 105 rather than 6, that's a function of the cache setting in the Sequence object itself.

[jseclen]

Hi chulett,

This is an example of the result:

ODBC records

Code Name Ind
---------------------
A100 Jorge 1
A200 Miguel 1
A300 Luis 1
A400 Luis 1
A500 Luis 1
A600 Luis 1
A700 Luis 1

Oracle Sequencer
.nextval = 5

Result (after lookup)

Code Name Ind nextVal
-------------------------------
A100 Jorge 1 5
A200 Miguel 1 5
A300 Luis 1 5
A400 Luis 1 6
A500 Luis 1 6
A600 Luis 1 6
A700 Luis 1 7

this is the problem, i expect a nextVal for each row, but the lookup assign the next val an a group of record, i think that is beacuse the lookup read the rows in block, some has other idea???

Thanks

[keshav0307]

set the lookup stage as sequential.

[Minhajuddin]

What is the query with which you are retrieving this nextval?

select seq.nextval from dual?

[jseclen]

I change to sequential, but obtain the same result.

The sql query is select 1 as ind, seq.nextvl from dual;

I use the ind = 1, to match with the lookup records.

[keshav0307]

try this

select orchastrate.code code, seq.nextvl from dual;

and use code a key on both link main and reference links.
i assume that code is the key

[keshav0307]

try this

select orchastrate.code code, seq.nextvl from dual;

and use code a key on both link main and reference links.
i assume that code is the key

[jseclen]

hi keshav0307,

i change my oracle query with :
select Orchastrate.correlativo as correlativo, sq_user.nextval as sequence from dual

but, when i run the job, fail, and show this error in the log:

LOOK_001: GenericQuery:esqlErrorHandler
Prepare failed for: GenericStmt_1
query is: select Orchastrate.correlativo as correlativo, sq_user.nextval as sequence from dual
sqlcode is: -904
esql complaint: ORA-00904: "ORCHASTRATE"."CORRELATIVO": invalid identifier . [genericquery.C:110]

[keshav0307]

do you have CORRELATIVO in the main link,
and do you have two coluns in the oracle lookup table stage correlativo and sequence

[jseclen]

hi keshav,

in my job i dont insert in the oracle yet, i was insert in a dataset and checking the value, the problem was with the display column, the sparse was working ok, the visualization it was the problem.

so, i dont need key to execute this query, because the lookup execute the query and return the value, and i can map it to the target.

Thanks for your help.

[keshav0307]

its not for the query to execute only, but it was to match the records.
when i tested it is working fine for me.

select ORCHESTRATE.correlativo correlativo, sq_user.nextval sequence from dual

[Minhajuddin]

I change to sequential, but obtain the same result.

The sql query is select 1 as ind, seq.nextvl from dual;

I use the ind = 1, to match with the lookup records.

You can't drag and drop the key in the lookup stage when you are using a Sparse lookup. So something is wrong with your job. Now All you have to do is run the following query:

select seq.nextval dummy from dual

Make the lookup as a sparse lookup (you need to set this up in the Oracle Enterprise Stage) and run your job.