HELP!!! - Field is NULL Record dropped
Moderators: chulett, rschirm, roy
HELP!!! - Field is NULL Record dropped
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
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
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Hi,
Why are you putting constraint:-
IsNull(<field>) Or Trim(<field>) = "" Or IsValid("timestamp", StringToTimestamp(<field>, "%yyyy%mm%dd%hh%nn%ss"))
Why are you putting constraint:-
IsNull(<field>) Or Trim(<field>) = "" Or IsValid("timestamp", StringToTimestamp(<field>, "%yyyy%mm%dd%hh%nn%ss"))
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Re: HELP!!! - Field is NULL Record dropped
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 wrote:IsValid("timestamp", StringToTimestamp(<field>, "%yyyy%mm%dd%hh%nn%ss"))
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
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.
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.
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
Then set your constraint to check the stage variable value: @TRUE or @FALSE.
Regards,
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
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
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.mmanes wrote: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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers