Lookup problem

rajkraj · Post by **rajkraj** » Thu Apr 05, 2007 3:00 pm

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

us1aslam1us · Post by **us1aslam1us** » Thu Apr 05, 2007 3:05 pm

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....

rajkraj · Post by **rajkraj** » Thu Apr 05, 2007 3:14 pm

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....

us1aslam1us · Post by **us1aslam1us** » Thu Apr 05, 2007 3:20 pm

Do not link the fields just write the constarint ...

rajkraj · Post by **rajkraj** » Thu Apr 05, 2007 3:59 pm

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 ...

us1aslam1us · Post by **us1aslam1us** » Thu Apr 05, 2007 4:56 pm

That constarint is for filtering the data so there might be some other fields which can be referenced as Keys.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 05, 2007 7:29 pm

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.

chulett · Post by **chulett** » Thu Apr 05, 2007 9:55 pm

You should be more assertive, Ray.

It will be dog slow unless you index the constraining columns.

vipshiva · Post by **vipshiva** » Mon Apr 09, 2007 1:17 am

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.

us1aslam1us · Post by **us1aslam1us** » Mon Apr 09, 2007 1:30 am

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?

vipshiva · Post by **vipshiva** » Mon Apr 09, 2007 2:15 am

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.....