Hi
we have a PX JOb where Source is a DataSet we are doing a Target Lookup on a Oracle Table the below ones are keys these are the same Keys in the Dataset to..
Oracle Keys
LANG_ID Char(2)
DECISION_CD Varchar(8)
Dataset Keys
LANG_ID CHAR(1)
DECISION_CD CHAR(8)
The problem is Lookup is Rejecting the Records and it is not doing the same when field in Oracle Table LANG_ID Char(2) is Created as VARCHAR().
we are giving a Condition If Trim(Move_Xfm.DECISION_CD_1) = '' And Trim(Move_Xfm.LANG_ID_1) = '' Then 0 else 1
This means if no records in Oracle Table Insert else Update.
O means Insert and 1 means Update initially we have no records in oracle.
Anyone through light on this.
TARGET LOOKUP
Moderators: chulett, rschirm, roy
I am not sure I understand exactly what you are doing but here are a few points to consider.
A char datatype means the data is a fixed length. It is therefore not possible for a char(1) field to match a char(2) field.
You can't trim a char because it is fixed length and trimming would shorten it. Try doing this in a transformer stage variable to see what happens. The stage variable needs to be a varchar to work.
It sounds like you are trying to use a link condition in the lookup to derive a value. The purpose of a condition is to test the data before doing the lookup. If the condition is not met you can specify whether to Drop, Fail, Continue or Reject.
It seems as though you need to provide some derivations in an earlier stage (a transformer) and use these in the lookup.
You need to understand what data is going to be in these key fields and choose the datatype appropriately.
A char datatype means the data is a fixed length. It is therefore not possible for a char(1) field to match a char(2) field.
You can't trim a char because it is fixed length and trimming would shorten it. Try doing this in a transformer stage variable to see what happens. The stage variable needs to be a varchar to work.
It sounds like you are trying to use a link condition in the lookup to derive a value. The purpose of a condition is to test the data before doing the lookup. If the condition is not met you can specify whether to Drop, Fail, Continue or Reject.
It seems as though you need to provide some derivations in an earlier stage (a transformer) and use these in the lookup.
You need to understand what data is going to be in these key fields and choose the datatype appropriately.