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'

asked 26 Aug '18, 10:33

Breck%20Carter's gravatar image

Breck Carter
32.5k5417241050
accept rate: 20%

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.

(27 Aug '18, 02:13) Vlad
Replies hidden

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.

(27 Aug '18, 02:18) Vlad

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

(27 Aug '18, 03:41) Volker Barth

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

(27 Aug '18, 09:07) 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 :)

(27 Aug '18, 09:11) Breck Carter

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.

(27 Aug '18, 13:08) Volker Barth
showing 1 of 6 show all flat view

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.

permanent link

answered 27 Aug '18, 16:15

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

Wow, a classic Glenn Answer, welcome back!

(27 Aug '18, 18:39) Volker Barth
Comment Text Removed
1

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

(29 Aug '18, 09:48) Breck Carter
Replies hidden

Or IQ?

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

(29 Aug '18, 10:31) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 26 Aug '18, 10:33

question was seen: 1,480 times

last updated: 29 Aug '18, 10:32