Page 1 of 1
Lookup problem
Posted: Thu Apr 05, 2007 3:00 pm
by rajkraj
Hi Folks,
How to specify this lookup condition in transformer;
Eff_str_dt<=ctrl_dt and Eff_end_dt>=ctrl_dt
Basically I am getting ctrl_dt as source and Eff_str_dt & Eff_end_dt in hash file.
Thanks,
Raj
Posted: Thu Apr 05, 2007 3:05 pm
by us1aslam1us
With Server Job, Pretty much as you have specified.
Code: Select all
In Constraints :
in.ctrl_dt >= Lkp.Eff_str_dt and in.ctrl_dt <= Lkp.Eff_end_dt
Or are you looking for something else....
Posted: Thu Apr 05, 2007 3:14 pm
by rajkraj
Eff_str_dt and Eff_end_dt are keys in hash file, I made link these two columns with ctrl_dt so hash file trying to match eff_dts with cntrl_dt but my condition is different. How to write this code in hash file key expression?
us1aslam1us wrote:With Server Job, Pretty much as you have specified.
Code: Select all
In Constraints :
in.ctrl_dt >= Lkp.Eff_str_dt and in.ctrl_dt <= Lkp.Eff_end_dt
Or are you looking for something else....
Posted: Thu Apr 05, 2007 3:20 pm
by us1aslam1us
Do not link the fields just write the constarint ...
Posted: Thu Apr 05, 2007 3:59 pm
by rajkraj
Those are key columnes, if u don't make a link it will give compilation error right?
us1aslam1us wrote:Do not link the fields just write the constarint ...
Posted: Thu Apr 05, 2007 4:56 pm
by us1aslam1us
That constarint is for filtering the data so there might be some other fields which can be referenced as Keys.
Posted: Thu Apr 05, 2007 7:29 pm
by ray.wurlod
You can not perform a range lookup to a hashed file using a Hashed File stage. The Hashed File stage can only look for exact key values (which it does via the file's hashing algorithm, which is extremely efficient).
You can perform such a lookup against a hashed file using a UV stage, which accesses the hashed file using SQL. It will probably be slow unless you index the constraining columns.
Posted: Thu Apr 05, 2007 9:55 pm
by chulett
You should be more assertive, Ray.
It will be
dog slow unless you index the constraining columns.
![Wink :wink:](./images/smilies/icon_wink.gif)
Posted: Mon Apr 09, 2007 1:17 am
by vipshiva
Raj,
Use OCI stage for lookup instead of Hased file.... and then
In the constraints
in.ctrl_dt >= Lkp.Eff_str_dt and in.ctrl_dt <= Lkp.Eff_end_dt
Regards,
Shiva.
Posted: Mon Apr 09, 2007 1:30 am
by us1aslam1us
vipshiva wrote:
Use OCI stage for lookup instead of Hased file.... and then
In the constraints
in.ctrl_dt >= Lkp.Eff_str_dt and in.ctrl_dt <= Lkp.Eff_end_dt
How that is going to change the scenario my friend?
Posted: Mon Apr 09, 2007 2:15 am
by vipshiva
Actually Raj mentioned that Eff_str_dt and Eff_end_dt are keys in hash file.
So when you don't connect with Eff_str_dt and Eff_end_dt fields, DS will give compilation error right?...
That's why I have given that solution ya....
Regards,
Shiva.....