|
As documented, multiple UNIONs are is documented from left to right:
What's the default precedence when combining multiple UNION and EXCEPT clauses? With SA 12.0.1.3298, UNION and EXCEPT (and INTERSECT?) seem to have the same precedence, and seem all to be computed from left to right. I.e. returns the same as the - obviously not valid ! - statement namely the rows "1" and "2", whereas just returns the row "1". Now what I would like to get is the union of two excepts - as in the very simplified example: Unfortunately, I seem to need parantheses to enforce this precedence but can't find the correct syntax - dbisqlc just issues a syntax error for this query. What am I doing wrong? |
|
The grammar rules used in SQL Anywhere include the following: The above (in human-speak) means that UNION and EXCEPT have equal precedence but will bind from left to right. INTERSECT has a higher precedence than UNION and EXCEPT and also binds left to right when more than one INTERSECT is used. The rest is left as an exercise for the reader (and hopefully should not be too hard to figure out) :-) I'm particularly grateful for the "human speak" part:) - Thanks for confirming Tyson's statement. FWIW, I've left a note in DCX yesterday to add that information.
(13 Apr '11, 11:33)
Volker Barth
|
|
I believe UNION and EXCEPT have equal precedence. INTERSECT has higher precedence. All three are evaluated left to right. Is there any official statement on these operations to confirm Tyson's statement (which fit my tests)?
(12 Apr '11, 16:05)
Volker Barth
|
|
Volker, You're last query seems to be correct. If you run it in DBISQL, no Syntax Error is reported. This seems to be a limitation with DBISQLC, and is not likely to be changed as DBISQLC is deprecated. If you have the java ISQL available, you should be able to run the query with that. Alternatively, you could wrap your actual union into a derived table and select from that. i.e. The above is a extremely simplistic example, just be sure that all columns have names. Thanks for the pointer - I'm gonna test that with DBISQL... I wasn't aware that these tools do their own syntax checks - as such I hadn't thought of dbisqlc limitations because it does allow UNION and EXCEPT in general.
(09 Apr '11, 07:02)
Volker Barth
Tyson, I've confirmed that the enclosed statements do work correctly with DBISQL. However, my title question is still unanswered...
(11 Apr '11, 03:20)
Volker Barth
2
This problem with select statements that start with an open parenthesis is fixed in dbisqlc 11.0.1.2591, 12.0.0.2670 and 12.0.1.3329. Dbisqlc does minimal parsing of most statements but it must do some to identify its own statements (e.g. INPUT). For statements not handled by dbisqlc itself, the first token also tells us whether to execute the statement or to open a cursor. Although other mechanisms exist for that today, they didn't when dbisqlc was first written.
(14 Apr '11, 15:13)
John Smirnios
Replies hidden
1
Well, John, your constant ability and readiness to fix dbisqlc issues won't help me to leave that deprecated tool behind ... thanks again:)
(15 Apr '11, 03:28)
Volker Barth
|