DSXchange

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???

jseclen wrote: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.

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

set the lookup stage as sequential.

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

select seq.nextval from dual?

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.

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

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

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]

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

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.

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

jseclen wrote: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.