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 )
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."
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
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.
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
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers