Looukp reading 1 row at a time
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 133
- Joined: Wed Mar 05, 2003 4:19 pm
- Location: Lima - Peru. Sudamerica
- Contact:
Looukp reading 1 row at a time
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???
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???
Saludos,
Miguel Seclén
Lima - Peru
Miguel Seclén
Lima - Peru
Re: Looukp reading 1 row at a time
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 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
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 133
- Joined: Wed Mar 05, 2003 4:19 pm
- Location: Lima - Peru. Sudamerica
- Contact:
Re: Looukp reading 1 row at a time
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
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
Saludos,
Miguel Seclén
Lima - Peru
Miguel Seclén
Lima - Peru
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
What is the query with which you are retrieving this nextval?
select seq.nextval from dual?
select seq.nextval from dual?
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 133
- Joined: Wed Mar 05, 2003 4:19 pm
- Location: Lima - Peru. Sudamerica
- Contact:
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]
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]
Saludos,
Miguel Seclén
Lima - Peru
Miguel Seclén
Lima - Peru
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 133
- Joined: Wed Mar 05, 2003 4:19 pm
- Location: Lima - Peru. Sudamerica
- Contact:
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.
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.
Saludos,
Miguel Seclén
Lima - Peru
Miguel Seclén
Lima - Peru
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>