Constraint accepted logic in transformer

pxraja · Post by **pxraja** » Fri Sep 05, 2008 9:51 pm

Hi all,

I want only the updated records to be inserted into the target table in oracle. I am having date field with that I am taking latest date to a hash file and looking up in a transformer.

In a transformer constraint if i am using lk.latest_date = inlink.EffDate,
its working fine
but the same is not working for inlink.EffDate>lk.latest_date

please any one tell me what's gone wrong? Whether constraint will not take this logic for Date field ?

your suggestions will be welcome

thanks in advance

ray.wurlod · Post by **ray.wurlod** » Fri Sep 05, 2008 10:17 pm

A lookup to a Hashed File can only be equality. So how are you looking up latest_date? Is the lookup always successful?

Are both dates in the same format, and is this a comparable format for a "greater than" comparison? For example YYYY-MM-DD is always comparable, but MM/DD/YYYY is not.

pxraja · Post by **pxraja** » Fri Sep 05, 2008 10:26 pm

Hi Ray,

I am using lk.latest_date as the key column and the format is same for both input and lookup. that is, yyyy-mm-dd format.

whether odbc stage could be the alternate for lookup hashfile to implement the logic

ray.wurlod · Post by **ray.wurlod** » Fri Sep 05, 2008 10:58 pm

Please answer all the questions. Is the lookup always successful? (Where is it not NULL is returned, which will always fail constraint logic.)

pxraja · Post by **pxraja** » Sat Sep 06, 2008 12:27 am

Hi Ray,

that particular field is NOT NULL so its working for the equality constraint.

Inlink.EffDate field will not be NULL. so its working for the equality constraint. As you said Hash file will work for equality constraint only can you suggest which will be used for non-equality constraint.

Thanks in Advance

ray.wurlod · Post by **ray.wurlod** » Sat Sep 06, 2008 1:14 am

It doesn't matter that it's not null. In a server job, if the lookup fails, NULL is returned. It's a left outer join.

pxraja · Post by **pxraja** » Sat Sep 06, 2008 6:32 am

Hi Ray,

I think, I am not guiding in different way.

My doubt is that whether the non equality constraint can be used for date field. how to get the records that is greater than lk.latest_date?

the job runs successfully for equality case. since Hash file will work for equality constraint (as you clarified).

there is other way also but I want to check whether the lookup will work for > or < or <> logics in the constraint ?

Thanks in Advance

chulett · Post by **chulett** » Sat Sep 06, 2008 7:13 am

If both of your dates are indeed in YYYY-MM-DD format and not null, than any comparison can be made in a constraint including 'non-equality'. Are you certain of your facts?