Sparse Lookup

madhuker · Post by **madhuker** » Mon Dec 20, 2010 3:15 am

Hi,

Facing issues with Sparse Lookup.

As i am trying to load data from Flat File to a Oracle Table using Sparse Lookup for validation.
Following is the error occured while loading the data .
Error number : ORA-01036: illegal variable name/number.

Source File structure:

col1 col2 col3
aaa bbb 1
bbb ccc 2
ccc ddd 3

Target Table structure:
col1 col2 col3
aaa bbb 1
bbb ccc 2
ccc ddd 3

Sparse Lookup table structure:
col3
1
2
3

In transformer, key columns used are col3 from both Sparse Lookup & Flat File.

Need your valuable assistance to fix this issue.

Thanks in Advance

ray.wurlod · Post by **ray.wurlod** » Mon Dec 20, 2010 4:32 am

Get the SQL out of your Oracle stage (it will be in the log) and inspect it to figure out what may have caused the -1036 error code. Without the SQL we can't provide any more cogent advice than that.

Sreenivasulu · Post by **Sreenivasulu** » Mon Dec 20, 2010 4:37 am

Hi Madhuker,

Its looks like a issue 'not related to sparse feature of datastage'

Regards
Sreeni

ray.wurlod · Post by **ray.wurlod** » Mon Dec 20, 2010 4:39 am

There is no "sparse feature" in server jobs.

chulett · Post by **chulett** » Mon Dec 20, 2010 7:47 am

Do an exact search here for that ORA-01036 error code, you're not the first person on the planet to cause it.

madhuker · Post by **madhuker** » Mon Dec 20, 2010 8:40 am

Hi,

Moment this error(ORA-01036: illegal variable name/numbe) occured, first thing i have done is executed SQL query in the toad.
Query is absolutely working fine. Query used in Lookup is (SELECT COL1 FROM TAB1).

I do accept that there is not saperate Sparse Lookup stage in the Server Edition, but from one of the previous discussions on
Sparse Lookup, it is defined as creating Lookup on the Database table directly.Thats the reason i mentioned it as Sparse Lookup.

In my job, my source is flatfile, Lookup dirtectly created on the Table and based on validation i am loading data into Target Table.

When i am using Hashfile, instead of Direct table lookup same query query is running fine.

I don't understand why the same query is acting abnormally. I would request you to assist me to fix the issue.

Thanks in Advance.

chulett · Post by **chulett** » Mon Dec 20, 2010 9:06 am

madhuker wrote:Moment this error(ORA-01036: illegal variable name/numbe) occured, first thing i have done is executed SQL query in the toad. Query is absolutely working fine.

All that means is the SQL's syntax is correct. It doesn't mean it will work in DataStage where a number of other factors come into play.

I would request you do the exact search suggested, all will be revealed.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 20, 2010 3:07 pm

For starters, the SQL for a lookup will (must?) contain a WHERE clause. Your alleged "test" SQL does not.

chulett · Post by **chulett** » Mon Dec 20, 2010 5:10 pm

I believe that is only true when there is a key field involved, which (oddly enough) is not required. I recall being able to retrieve a sequence from Oracle by doing a 'select x.nextval from dual' lookup without needing a fake where clause. It would just fire for every row with no input from the source.

I must leave that to others to prove or disprove, however.

madhuker · Post by **madhuker** » Tue Dec 21, 2010 4:47 am

Hi,

Tried in every possible way to fix the issue but could not. Now my doubt is can we use Oracle OCI stage as dirtect lookup from database in 8x server version. If 'YES' , am i using the right stage. I know that we can implement in 7x server version, as i have used but not sure about 8x version. Need your help.

Thanks in Advance

chulett · Post by **chulett** » Tue Dec 21, 2010 8:01 am

You need to help us help you. We have no idea what 'every possible way' you tried and I'm not willing to guess. Did you try the search suggested? What exactly did you try to solve this problem?

Worst case, we need to know everything you can tell us about your OCI stage. For example - what is your exact SQL? What columns are defined in the stage, order and datatype? Which ones are marked as 'key' columns?

Nothing about this should have changed from the 7.x to the 8.x release, especially considering this is Server we're talking about. However, it may also be beneficial to know what exact 8.x version you are running and if you are any kind of current on your fixpacks.

madhuker · Post by **madhuker** » Wed Dec 22, 2010 1:10 am

Hi,

I am using Websphere Information server Edition version 8.1.

Let me explain in brief about the job i am trying to execute.

In my job source system is flatfile which contains 4 attributes. Following are the source file structure.

Column Name Col1 Col2 Col3 Col4
DataTypes Varchar2(10) Varchar2(10) number(10) number(10)
Data aaa bbb 1 2
bbb ccc 2 3
ccc ddd 3 4
ddd eee 4 5

Here Key column is Col3.

I am extracting these 4 attributes into Transformer where i am validating the data with the Database Lookup table.

In my Lookup, I am extracting only one column that is Col1 from tab1 table. Query used to extract the data in Lookup is

Select Col1 from Tab1;

Datatype of the Col1 in lookup is Number(10) and it is considered as Key column.

The above lookup column contains the following data

Col1
1
2
3
4

My Target table is Tab2.
Datastructure of the Target table is

Col1 Varchar2(10),
Col2 Varchar2(10),
Col3 number(10),
Col4 number(10).

I am just trying to insert the data into target table.

Hope i have provided the details required.

Thanks in Advance.

HariK · Post by **HariK** » Thu Dec 30, 2010 4:58 am

Select Col1 from Tab1;

The query looks incomplete for a reference input.
Try converting the SQL to Auto generated and check.

prasad.bodduluri · Post by **prasad.bodduluri** » Tue Dec 13, 2011 12:56 am

use hashfile as lookup instead of database table, it will slove issue.

snehal15101982 · Post by **snehal15101982** » Tue Dec 13, 2011 4:32 am

This is definitely an error related to oracle.Try to do select count(*) from table and run the query in toad.I am sure you will not be able to run it.
or run the query in the toad and in the data grid do a ctrl+A.You will get the row which has the issue.This is not a datastage error.