May 31, 2002 at 8:38 am
I have noticed that SQL 2000 Enterprise Manager has the bad habit of taking out parentheses from logical expressions in constraints. For example, I right clicked on one of my tables and selected "Design Table", and then clicked the "Manage Constraints" button. I enter the following constraint:
([TRAN_CATEGORY] = 'C' and [LC_ACTUAL_PAY_AMT] = 0)
or [TRAN_CATEGORY] = 'P'
When saved, Enterprise manager "edits" the constraint, moving the parentheses:
([TRAN_CATEGORY] = 'C' and [LC_ACTUAL_PAY_AMT] = 0
or [TRAN_CATEGORY] = 'P')
which is not the same logic at all! Does anyone know how to get around this behavior? It seems necessary to put this kind of statement into a trigger so it won't get mangled.
Thanks much,
Peter
May 31, 2002 at 8:41 am
Have you tried putting parens around the whole expression before saving? I think it is wanting the full expression enclosed. Oh well, my suggestion.
Jody
May 31, 2002 at 8:54 am
Good idea Jody, but it doesn't help.
May 31, 2002 at 9:09 am
Interesting choice of words. Hopefully EM doesn't have any habits it can learn or unlearn. 🙂
I would suspect this is a bug, however it should work based on the precendence of the AND and OR operators.
If I put the parens around the OR, then they are saved.
Steve Jones
May 31, 2002 at 9:24 am
Steve, it's funny that your version keeps parens some of the time, 'cause mine doesn't. I wonder if I'm missing something.
You're right about the precedence giving the correct result my example even without the parens, so I changed the expression slightly to something that would not be correct without parens:
([TRAN_CATEGORY] = 'C' and [LC_ACTUAL_PAY_AMT] = 0)
or ([TRAN_CATEGORY] = 'P' and [LC_ACTUAL_PAY_AMT] > 0)
My EM [build 2195, service pack 2] still strips the parens.
May 31, 2002 at 9:26 am
Actually it is the same logic. WHen items are parse AND parse together and OR parse seperately, if you have X AND Y OR A AND B it will parse as (X AND Y) OR (A AND B) there is not need to specify a seperator however if you want X AND Y OR A AND B to be read as X AND Y AND B OR A AND B then you have to use the ()'s to set off like so (X AND Y OR A) AND B removing the parenthesis would not have the same effect in that case. EM just has a tendency to remove unneeded charcters when it parses these (don't know whose idea that was and it make have some specific bennifit).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 31, 2002 at 9:43 am
Thank you. I stand corrected.
So here is the deal. EM parses the sql expression and strips out UNNEEDED parentheses, while leaving needed parens in place. For example, I tested with
(A or B) and (C or D)
and it correctly leaves the parens in place.
Having said that, I still don't like EM's "habit" of stripping some parens. This can hurt readablity and ultimately lead to *real* errors.
Thanks everyone for your help!
Peter
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply