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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy