Beware of ORDER BY syntax error in IF EXISTS SELECT TOP

[Breck_Carter]

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'

[Vlad]

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.

[Vlad]

The note here says that IF statement and IF condition are not the same. https://help.sap.com/viewer/40c01c3500744c85a02db71276495de5/17.0/en-US/8170fd656ce2101484578af3b084... Maybe that is why the error happens in different cases only.

[VolkerBarth]

Well, the same error appears here for IF expression and IF statement (see 1st and 2nd sample), so here they are treated identically:)

[Breck_Carter]

> 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).

[Breck_Carter]

If I was a gambler, I'd place a small wager on the parser YACC input being broken unnecessarily strict and that the query engine is perfectly capable of handling the expression 🙂

[VolkerBarth]

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.

[Former Member · Accepted solution]

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.

[VolkerBarth · Accepted solution]

Wow, a classic Glenn Answer, welcome back!

[Breck_Carter · Accepted solution]

Yes, welcome back! ( I thought you were shackled to Hanna 🙂

[VolkerBarth · Accepted solution]

Or IQ?

FWIW, there might be a choice to MERGE "gpaulley" and this legendary user🙂