May 28, 2010 at 2:33 pm
For example, the condition
1 == 0 ? 1/0 : 1
should evaluate to 1 (the false expression) but an error is raised due to the divide by zero, which (I think) should not be evaluated.
In my package, I want to set the value of string variable "a" to be the left-most characters of string variable "b", up to but not including the first "." character. If variable "b" does not contain a "." character, or "." is the 1st character, I want variable "a" to be empty.
The expression I am using for variable "a" is:
FINDSTRING(@[User::b], ".", 1) > 1 ? SUBSTRING(@[User::b], 1, FINDSTRING(@[User::b], ".", 1)-1 ) ) : ""
Logically, this means "If variable "b" contains a "." character at position 2 or higher, get the characters from position 1 to the position before the ".", otherwise return an empty string.
If variable "b" has a value like "abc.def", it correctly returns "abc".
But if variable "b" has a value like "abcdef", it causes an error ("The length -1 is not valid for function "SUBSTRING". The length parameter cannot be negative. ..."
Why is the <<true expression>> evaluated when the condition is false?
Is there another way to accomplish this?
May 31, 2010 at 3:56 am
This was removed by the editor as SPAM
May 31, 2010 at 9:48 am
I tried your suggestion before making the post - it behaved the same way. I have since read elsewhere that VB seems to be the last programming language that behaves this way - others evaluate only the expression required based on the result of the condition.
I solved this problem by adding the string " ." to the FINDSTRING in the true expression:
FINDSTRING(@[User::b], ".", 1) > 1 ? SUBSTRING(@[User::b], 1, FINDSTRING(@[User::b] + " .", ".", 1)-1 ) ) : ""
so that even if the condition is false, the true expression will still work, as the FINDSTRING in it will return a value greater than 1.
While this now works for me, it would still be a problem if you were trying to evaluate a numeric expression containing a divisor, and want to avoid a divide by zero by testing the divisor in the condition.
I believe you are right that this is something that MS must not only comment on, but fix!
June 1, 2010 at 3:05 am
The problem you described is an old caveat that exist (or did exist) in most programming languages.
Usually this is with the iif or iff syntax.
Most programming languages either deprecated the use of these functions, or fixed the problems.
Apparently MS didn't 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply