String To Decimal Conversion Error
Posted: Mon Nov 05, 2018 6:33 pm
Have a Parallel Job to load the source data into a staging table. Source file has all fields comes in Varchar Format and during loading , i'm converting string to decimal for all decimal fields. If we have a numerical value then loading job completed successfully with no warnings. But if any one of the decimal field has blanks or nulls than i'm getting warning message "Conversion error calling conversion routine decimal_from_string data may have been lost".
For (e.g), my source file has the below columns
ID,INT_RATE,INT_AMT,INT_PEND
Source Datatype
ID - VARCHAR(3),INT_RATE-VARCHAR(7),INT_AMT-VARCHAR(7),INT_PEND-VARCHAR(7)
Values are:
100|10.1|12.2|25.5
101|22.2|55.67|33.78
102||||
Target Datatype
ID - VARCHAR(3),INT_RATE-DECIMAL(5,2),INT_AMT-DECIMAL(5,2),INT_PEND-DECIMAL(5,2)
In order to avoid the warning, have coded like below:
if (INT_RATE = ' ' OR IsNUll(INT_RATE) =1) then Stringtodecimal(0) Else
Stringtodecimal(INT_RATE).
Even after added this condition, i'm getting the warnings. Can someone help me to get rid of this warnings. Thanks..
For (e.g), my source file has the below columns
ID,INT_RATE,INT_AMT,INT_PEND
Source Datatype
ID - VARCHAR(3),INT_RATE-VARCHAR(7),INT_AMT-VARCHAR(7),INT_PEND-VARCHAR(7)
Values are:
100|10.1|12.2|25.5
101|22.2|55.67|33.78
102||||
Target Datatype
ID - VARCHAR(3),INT_RATE-DECIMAL(5,2),INT_AMT-DECIMAL(5,2),INT_PEND-DECIMAL(5,2)
In order to avoid the warning, have coded like below:
if (INT_RATE = ' ' OR IsNUll(INT_RATE) =1) then Stringtodecimal(0) Else
Stringtodecimal(INT_RATE).
Even after added this condition, i'm getting the warnings. Can someone help me to get rid of this warnings. Thanks..