HELP!!! - Field is NULL Record dropped

[mmanes]

Hi,
I've a big problem for me.

I've a job: Ora Ent ---> TX ---> Ora Ent
The problem is on trasformer and is as follows:

APT_CombinedOperatorController,2: Field '<field>' from input dataset '0' is NULL. Record dropped.

It's a warning.

the constraint is:

IsNull(<field>) Or Trim(<field>) = "" Or IsValid("timestamp", StringToTimestamp(<field>, "%yyyy%mm%dd%hh%nn%ss"))

derivation is:

If IsNull(<field>) Or Trim(<field>) = "" Then SetNull() Else StringToTimestamp(<field>,"%yyyy%mm%dd%hh%nn%ss")

Source field (Oracle) is: Varchar2(50) Nullable (source value=NULL)
Target field (Oracle) is: DATE Nullable

Thank you in advance,
m

[prasson_ibm]

Hi,

Why are you putting constraint:-

IsNull(<field>) Or Trim(<field>) = "" Or IsValid("timestamp", StringToTimestamp(<field>, "%yyyy%mm%dd%hh%nn%ss"))

[chulett]

IsValid("timestamp", StringToTimestamp(<field>, "%yyyy%mm%dd%hh%nn%ss"))

IsValid() checks a string to see if it can be successfully converted to the target data type. If it is valid, then you convert it - you do not do it inside the validity check.

[mmanes]

I tested it on other jobs and it works perfectly

[mmanes]

does anyone know the solution?

[chulett]

I tested it on other jobs and it works perfectly

That doesn't make it correct. Learn how the functions work and leverage them correctly to minimize unintended problems.

[mmanes]

Prasson_ibm put the costraint because I have a reject link

[chulett]

I tested it on other jobs and it works perfectly

That doesn't make it correct. Learn how the functions work and leverage them correctly to minimize unintended problems.

[mmanes]

Therefore, the syntax would be

IsValid("timestamp", <field>)?

[chulett]

Basically yes as long as your field is a string. Depending on your version a third parameter is optionally available to tell it the format of the string, if appropriate to the data type. This is all documented, best to check there if you are unsure.

[mmanes]

I checked the documentation before using it and I understood what you said but did not work. I will try again but the problem is not this.

The problem is what I wrote in the post.

The same code works fine if the source is a flat file but does not work (WARNING dropping records) if the source is a Oracle Enterprise stage.

[jwiles]

What is the datatype of the column in Oracle, and what is it within DataStage? Is the column in Oracle nullable and does it contain nulls (no date, no empty string)?

[mmanes]

In Oracle and DataStage the datatype is the same

Source field (Oracle) is: Varchar2(50) Nullable (source value=NULL)
Target field (Oracle) is: DATE Nullable

in DataStage (Transformer) the source column is VarChar 50 Nullable
and the target column is Timestamp 38 Nullable

There're no fatal errors but only warnings like:
APT_CombinedOperatorController,2: Field '<field>' from input dataset '0' is NULL. Record dropped.

then, no records are inserted.

[jwiles]

The transformer will evaluate the entire logic of a complex condition, even if one of the conditions of an OR construct is already true. This can cause issues like this when null handling is combined with other conditions as in your logic. The sequential file records make it through probably because the columns are empty strings instead of nulls. The Oracle rows don't because they are actually nulls, which can't be handled by the Trim or IsValid functions.

Two possible options:

1) Use NullToEmpty() to copy the source column into a stage variable, then evaluate the stage variable (instead of the source column) in your constraint without the IsNull() logic.
2) Move the constraint logic to the derivation of a stage variable and separate the IsNull from the rest of the condition, something like

Code: Select all

If IsNull(field) then @TRUE else if Trim(field) = "" or IsValid(...) then @TRUE else @FALSE

Then set your constraint to check the stage variable value: @TRUE or @FALSE.

Regards,

[chulett]

I checked the documentation before using it and I understood what you said but did not work. I will try again but the problem is not this.

Sorry, I should have made my response more clear. I wasn't trying to solve your problem directly, just pointing out an error in your expression with the use of the function... which for all we knew at that point could have had a bearing on your issue.