topic Dynamic SQL returns strange error if ORDER BY is missing in Application Development and Automation Discussions

Dynamic SQL returns strange error if ORDER BY is missing

tudor_teoteoi — Wed, 27 Jan 2021 20:09:39 GMT

Dear community,

I built this dynamic SQL to handle selection based on parameters passed to a REST services (e.g. $sort, $filter):

SELECT (iv_column_syntax) FROM (iv_source)
WHERE (iv_cond_syntax)
ORDER BY (lv_orderby)
INTO CORRESPONDING FIELDS OF TABLE @et_entityset
UP TO @lv_top ROWS
OFFSET @lv_skip.

I noticed that deleting the ORDER BY clause in bold triggers a compile error:

Couldn't find Error Message: E SELECT 605 because the system is running
with the database objects from release 740. The system has not
completely upgraded to release 749.

Same happens if lv_orderby is present, but empty. It does work when it is set to 'PRIMARY KEY', for instance.

On the other hand, old syntax SQL works. But I can't use OFFSET, as it available only in the new syntax.

SELECT (iv_column_syntax) FROM (iv_source)
UP TO lv_top ROWS
"OFFSET lv_skip
INTO CORRESPONDING FIELDS OF TABLE et_entityset
WHERE (iv_cond_syntax)
ORDER BY (lv_orderby).

Is this really due to an update currently running?

Re: Dynamic SQL returns strange error if ORDER BY is missing

joltdx — Thu, 28 Jan 2021 05:48:52 GMT

Hi!

Regarding the strange error message: Please check KBA 2346369. It's not specifying 749 version specifially, but 7.42 and it might still be valid for you. Are you performing an upgrade currently, and is your code not yet unicode compliant, then that's probably it.

Regarding the SELECT statement and the ORDER BY thing. Yes, ORDER BY is required if you're using OFFSET, to ensure that the entries read are in the same expected order to get the expected result... Link to documentation. So it has to be there to run.

Re: Dynamic SQL returns strange error if ORDER BY is missing

tudor_teoteoi — Thu, 28 Jan 2021 09:10:01 GMT

Hi Jörgen,

Thanks for your answer. Indeed, I'm in this situation: ORDER BY is required if you're using OFFSET. The probably actually appeared when I tried SELECTing from JOINed tables, as the original ORDER BY PRIMARY KEY didn't work any more, so I replaced it with blank.

I can only blame the SAP compiler for a very misleading error message, as it took me hours to reach to the actual cause.

Best Regards,
Tudor