July 29, 2014 at 2:45 am
Hi all
I have been trying to check a row of data that is coming from an SQL DB. It should only hold int values but some fields hold 'Null' values. I need to make sure that all the fields are of int type.
This is a representation of the data
monday_standard_hrs
6
1
NULL
1
1
1
NULL
NULL
2
2
I have been looking around and have got stuck. I think I need to use a derived column with an expression which is along the lines of.
ISNULL( [monday_standard_hrs]) || TRIM( [monday_standard_hrs]) == "" ? (DT_I4) 0 : (DT_I4) [monday_standard_mins]
If anyone can help
Thanks
Mark
July 29, 2014 at 3:57 am
So if it is NULL, you want to convert it to 0?
What problems do you encounter right now?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 29, 2014 at 5:59 am
Hi
Yes if the field is currently null i want to convert the Null to 0.
I am currently having issues woth the expression and flags an error of the following
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [Derived Column [1524]]: The function "TRIM" does not support the data type "DT_I4" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.
Error at Data Flow Task [Derived Column [1524]]: Evaluating function "TRIM" failed with error code 0xC0047089.
Error at Data Flow Task [Derived Column [1524]]: Computing the expression "ISNULL( [monday_standard_hrs]) || TRIM( [monday_standard_hrs]) == "" ? (DT_I4) 0 : (DT_I4) [monday_standard_mins]" failed with error code 0xC00470C5. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.
Error at Data Flow Task [Derived Column [1524]]: The expression "ISNULL( [monday_standard_hrs]) || TRIM( [monday_standard_hrs]) == "" ? (DT_I4) 0 : (DT_I4) [monday_standard_mins]" on "input column "monday_standard_hrs" (1536)" is not valid.
Error at Data Flow Task [Derived Column [1524]]: Failed to set property "Expression" on "input column "monday_standard_hrs" (1536)".
July 29, 2014 at 6:11 am
Well, the TRIM function is a string function, so you cannot use it on integer columns.
Your expression can be simplified to this:
ISNULL( [monday_standard_hrs]) ? (DT_I4) 0 : (DT_I4) [monday_standard_mins]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 29, 2014 at 7:12 am
Awersome
thanks for the help 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply