July 31, 2011 at 11:36 pm
Hi All,
I am trying to change column [division\buyer] when column [division code] is blank or null to "Starcom - Non Tech".
It seems that the right hand side TRIM() of my condition statement works but not the left side ISNULL(). The code is black and it changes [division\buyer] correctly when it is blank\"", but not when there is a null in the column [division code].
Derived Column Name: Division\Buyer
Derived Column: Replace 'Division\Buyer'
Expression:
(line_id)==1 && ISNULL([Division Code]) || TRIM([Division Code]) == "" ? " Starcom - Non Tech" : [Division\Buyer]
The data type is NVCHAR(50) in the DB and in the derived editor it is Unicode String[DT_WSTR].
Thanks
July 31, 2011 at 11:50 pm
You are mixing AND (&&) and OR (||) in one condition, which can result in unexpected results. Try putting brackets so that you are sure that the conditional logic is followed.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2011 at 12:44 am
I have changed it to make more simple and it still doesn't work, when I query the DB there is actually a null record in that column [Division Code].
ISNULL([Division Code]) ? "Starcom - Non Tech" : [Division\Buyer]
August 1, 2011 at 5:32 am
Are you sure it is a null value and not a string with the value NULL?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2011 at 7:34 pm
I got it working by removing the null in the Select statement, the dervied editor was detecting the NULL.
ISNULL(optional_3l, '') AS [Division Code]
So the column is always empty or has a entry now so the dervied editor conditions work perfectly. Thanks for the replies.:-P
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply