Historically, there have been many [cough] arcane limitations on coding a SELECT as an expression; this may be another. SELECT @@VERSION; '16.0.0.2512' -- IF EXISTS SELECT TOP ORDER BY expression fails BEGIN DECLARE @yn VARCHAR ( 1 ); SET @yn = IF EXISTS ( SELECT TOP 1 START AT 100 * FROM RowGenerator WHERE row_num < 50 ORDER BY row_num ASC ) THEN 'Y' ELSE 'N' END IF; SELECT @yn; END; Could not execute statement. Syntax error near 'ORDER' on line 7 SQLCODE=-131, ODBC 3 State="42000" Line 1, column 1 (Continuing after error) -- IF EXISTS SELECT TOP ORDER BY statement fails BEGIN DECLARE @yn VARCHAR ( 1 ); IF EXISTS ( SELECT TOP 1 START AT 100 * FROM RowGenerator WHERE row_num < 50 ORDER BY row_num ASC ) THEN SET @yn = 'Y'; ELSE SET @yn = 'N'; END IF; SELECT @yn; END; Could not execute statement. Syntax error near 'ORDER' on line 7 SQLCODE=-131, ODBC 3 State="42000" Line 1, column 1 (Continuing after error) -- SELECT TOP INTO ... IF IS NOT NULL works (example 1) BEGIN DECLARE @yn VARCHAR ( 1 ); DECLARE @dummy_result SMALLINT; SET @dummy_result = NULL; SELECT TOP 1 START AT 100 row_num INTO @dummy_result FROM RowGenerator WHERE row_num < 50 ORDER BY row_num ASC; IF @dummy_result IS NOT NULL THEN SET @yn = 'Y'; ELSE SET @yn = 'N'; END IF; SELECT @yn; END; @yn 'N' -- SELECT TOP INTO ... IF IS NOT NULL works (example 2) BEGIN DECLARE @yn VARCHAR ( 1 ); DECLARE @dummy_result SMALLINT; SET @dummy_result = NULL; SELECT TOP 1 START AT 100 row_num INTO @dummy_result FROM RowGenerator WHERE row_num < 200 ORDER BY row_num ASC; IF @dummy_result IS NOT NULL THEN SET @yn = 'Y'; ELSE SET @yn = 'N'; END IF; SELECT @yn; END; @yn 'Y' |
Breck - as far as I can remember, EXISTS did have a restriction on not supporting ORDER BY. The game-changer was the support of SELECT TOP and I think what you're seeing is an unintended restriction. I'll have a look. There isn't really any reason why queries (1) and (2) shouldn't work. Wow, a classic Glenn Answer, welcome back!
(27 Aug '18, 18:39)
Volker Barth
Comment Text Removed
Replies hidden
Or IQ? FWIW, there might be a choice to MERGE "gpaulley" and this legendary user:)
(29 Aug '18, 10:31)
Volker Barth
|
I don’t know the answer (yet), but you are not the only one who faces this error. The same thing is impossible in Oracle as well: https://stackoverflow.com/questions/44403662/order-by-in-exists-sub-query
I guess this is because EXISTS checks for ANY result from your subquery. And ORDER is redundant, since it doesn’t affect the record presence.
The note here says that IF statement and IF condition are not the same. https://help.sap.com/viewer/40c01c3500744c85a02db71276495de5/17.0/en-US/8170fd656ce2101484578af3b0843edf.html Maybe that is why the error happens in different cases only.
Well, the same error appears here for IF expression and IF statement (see 1st and 2nd sample), so here they are treated identically:)
> ORDER is redundant, since it doesn’t affect the record presence
That would make sense, if it was true, but it is not... SELECT TOP absolutely positively depends on ORDER BY, and it fact ORDER BY is really a requirement for the correct use of TOP (which should be a Characteristic Error :)
As proof, please look at code samples 3 and 4, which both work, and which are both identical except for the WHERE clause... which yields different results.
=====
Note that I am not asking for a change, just warning folks that SELECT queries used as expressions continue to have limitations.
I am profoundly grateful for limitations that have been relaxed ELSEWHERE (e.g., ORDER BY inside queries that are UNIONed together).
If I was a gambler, I'd place a small wager on the parser YACC input being
brokenunnecessarily strict and that the query engine is perfectly capable of handling the expression :)Well, I'd say for an EXISTS test the order of the outer query block is not important as it does not influence the number of rows. That would even be true for a TOP n query, with n > 0 it does not make a difference when the TOP clause would be dropped.
However, TOP n START AT m certainly does have an influence on the number of rows returned. And that is used here.