My reference ora stage contains a column salarythousands with decimal datatype
Im performing left outer join of above reference ora stage with my source ora stage and passing field value to transformer.
in transformer im doing calculation as:
stagevariable1 with datatype decimal 11,
stagevariable1 = If IsNull(lk.salarythousands) Or Trim(lk.salarythousands) = "" Then 0
Else ((lk.salarythousands * 1000) / 12)
then passing to target column with varchar datatype as DecimaltoString(stagevariable1)
but still im getting the below error:
Trans_Land,3: Field 'salarythousands' from input dataset '0' is NULL. Record dropped.
Please let me know how to resolve this issue.
Thanks,
NULL record dropped issue for decimal to varchar col
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
Is it possible that you are using the column "salarythousands" to derive some other column without handling nulls than the one mentioned?
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
I doubt that the conversion is causing the issue. Please check if any of your other target columns are using the column - "salarythousands". If yes, are any functions applied on it ?
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Which version of DataStage are you working with?
In 8.5 and higher this should work when Legacy-Null-handling is switched off on the transformer properties.
In versions prior to 8.5 it was not allowed to pass NULL-values to Transformer-functions as input. In case this happened on any column or stage-variable the complete record was dropped.
And then: why do you use a string-function on a column with decimal-datatype in the first place:
Use
stagevariable1 = If NullToZero(lk.salarythousands) = 0 Then 0
Else ((lk.salarythousands * 1000) / 12)
In 8.5 and higher this should work when Legacy-Null-handling is switched off on the transformer properties.
In versions prior to 8.5 it was not allowed to pass NULL-values to Transformer-functions as input. In case this happened on any column or stage-variable the complete record was dropped.
And then: why do you use a string-function on a column with decimal-datatype in the first place:
Use
stagevariable1 = If NullToZero(lk.salarythousands) = 0 Then 0
Else ((lk.salarythousands * 1000) / 12)
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
My new learning for the day. Don't know how I missed it this long!BI-RMA wrote:In 8.5 and higher this should work when Legacy-Null-handling is switched off on the transformer properties.
In versions prior to 8.5 it was not allowed to pass NULL-values to Transformer-functions as input.
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.