DSXchange

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

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.

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

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

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

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.

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

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?