Lookup issue

svkworld · Post by **svkworld** » Thu Nov 01, 2007 3:15 pm

I've a source table such as follows:

id, date_of_service

The lookup table has following information

id, eff_date, term_date

I would like to retrieve matching row from lookup table. where id=id and date_of_service BETWEEN eff_date and term_date. I am unable to code the 2nd condition in datastage. Any help is greatly appreciated! (including pointing to right threads

)

chulett · Post by **chulett** » Thu Nov 01, 2007 3:19 pm

Search for 'range lookup' amongst the forums and you shall find what you seek, Pilgrim.

svkworld · Post by **svkworld** » Thu Nov 01, 2007 3:33 pm

Thnaks, I searched the forum and follow the steps accordingly to use the UV stage for look up. But when I specify the hashed file name as table name and do a view data it says "table doesnot exist."

Any ideas?

ArndW · Post by **ArndW** » Thu Nov 01, 2007 3:50 pm

Are you certain that the hashed file exists (define it in a job, use "view data" in a Hashed file stage)? If so, how did you declare it as a UV stage?

svkworld · Post by **svkworld** » Thu Nov 01, 2007 4:20 pm

Yes, I did create the HASH File as a seperate job and I am able to view data.

svkworld · Post by **svkworld** » Thu Nov 01, 2007 4:21 pm

Yes, I did create the HASH File as a seperate job and I am able to view data from this job. It throws me an error only when I try to view it from the UV Stage.

ArndW · Post by **ArndW** » Thu Nov 01, 2007 4:28 pm

Well, then how did you define it in the UV stage? with which connection?

chulett · Post by **chulett** » Thu Nov 01, 2007 5:20 pm

If you created the hashed file 'pathed' in a directory rather than the account, you need to create a VOC pointer to it before you can use it with the UV stage.

svkworld · Post by **svkworld** » Fri Nov 02, 2007 4:28 am

I created the hashed file in the account not in apathed directory.

And in the UV stage I specified DataSource as localuv and imported the hashed file table definition.

What am I missing?

ArndW · Post by **ArndW** » Fri Nov 02, 2007 4:35 am

Try changing the table to "VOC" and do a view data on it? Do you get the same error message or one stating bad column names?

irajasekharhexa · Post by **irajasekharhexa** » Fri Nov 02, 2007 4:43 am

ArndW wrote:Try changing the table to "VOC" and do a view data on it? Do you get the same error message or one stating bad column names?

Why con't we use the Transformation stage and stage vatriables with logical operators to find out the date range like Actualdate>=rangedate1 and Actualdate<=rangedate1. And then pass it to next stage.

svkworld · Post by **svkworld** » Fri Nov 02, 2007 4:44 am

I entered VOC in the table name and this is the error I got:

UniVerse_27.DSLink19: DSD.BCIOpenR call to SQLExecDirect failed.

SQLSTATE=S1000, DBMS.CODE=950088
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: syntax error. Unexpected symbol. Token was "ID". Scanned command was FROM VOC SELECT ID

ArndW · Post by **ArndW** » Fri Nov 02, 2007 4:49 am

That is a good message, it means the VOC is accessible. It also means that you have not declared your hashed file as a local one.

svkworld · Post by **svkworld** » Fri Nov 02, 2007 5:00 am

This is what I have done in the job that loads to the Hashed file:

ODBC --> Xformer--> Hashed file

In the General tab of the Hashed file stage I selected the Use Account name and picked the one I want from the drop down list(this is where I created teh table definition in the Manager as well).

In the Inputs tab I selected the file name from teh drop down list, which I had created in the Manager under Table definition.

Did I miss anything or go wrong somewhere?

chulett · Post by **chulett** » Fri Nov 02, 2007 7:07 am

svkworld wrote:In the General tab of the Hashed file stage I selected the Use Account name and picked the one I want from the drop down list(this is where I created teh table definition in the Manager as well).

What actual value did you pick? This locks your hashed file to always live in that account and not move up along with the jobs to other Projects - test / qa / production / etc.

The vast majority of the time the account should be left blank to mean the 'current' account. Try changing that and rerunning your hashed file creation job.