topic Re: Sorting Order in SQL Queries in Application Development and Automation Discussions

Sorting Order in SQL Queries

former_member196555 — Thu, 17 Jul 2014 06:00:33 GMT

Hi Colleagues,

We notice difference in the output when executing the SELECT queries on traditional DB and HANA. We are using side car approach so we have both the databases.

SELECT Query 1 without HANA

SELECT * FROM

EKPO

INTO TABLE LT_EKPO

FOR ALL ENTRIES IN LT_EKKO

WHERE ebeln = lt_ekko-ebeln

AND werks IN s_werks.

SELECT Query 2 with HANA

SELECT * FROM

EKPO

connection (gv_dbcon)

INTO TABLE LT_EKPO

FOR ALL ENTRIES IN LT_EKKO

WHERE ebeln = lt_ekko-ebeln

AND werks IN s_werks.

Both the queries give me the same result set, but with different order.

What could be the reason? and how this should be avoided.

Thank you.

Regards,

Harshad Mishrikotkar.

Re: Sorting Order in SQL Queries

thomasgauweiler — Thu, 17 Jul 2014 06:27:20 GMT

Dear Harshad,

if you do not specify the order with an ORDER BY clause, then the order of the data returned by the database is undefined. Each database returns the data according to its internal execution mode of the select statement.

As HANA can evaluate the result in parallel activities it can even differ when you repeat the same statement again. Also other database might return the result in different orders.

Therefore you MUST use an ORDER BY clause if the order is relevant for you.

Best regards, Thomas

Re: Sorting Order in SQL Queries

former_member196555 — Thu, 17 Jul 2014 06:54:31 GMT

Hi Thomas,

Thank you for your inputs.

I noticed a difference when using the ORDER BY Clause as well.

SELECT MBLNR

DMBTR

WERKS

INTO TABLE IT_MSEG

FROM MSEG connection (gv_dbcon)

FOR ALL ENTRIES IN tempqals

WHERE mblnr = tempqals-mblnr

AND werks = tempqals-werk

ORDER BY PRIMARY KEY.

Here I see the result set is sorted with MBLNR but it is not sorted on all the primary key fields.

Am I missing here something? Do we need to SELECT all the primary key fields as well in order to use ORDER BY PRIMARY KEY?

Thank you.

Regards,

Harshad.

Re: Sorting Order in SQL Queries

thomasgauweiler — Thu, 17 Jul 2014 08:12:56 GMT

Dear Harshad,

ORDER BY PRIMARY KEY requires that all key fields occur in the select list. With a 740 system you will even get an syntax error for your statement.

Best if you use a ORDER BY with an explicit list of fields according to the needs of your following logic.

BTW: We have some tooling to detect such problematic situations.

Please refer to the SCN document "Best Practice Guide - Considerations for Custom ABAP Code During a Migration to SAP HANA" at http://scn.sap.com/docs/DOC-46714

Best Regards, Thomas

Re: Sorting Order in SQL Queries

Former Member — Fri, 03 Jul 2015 09:31:30 GMT

Dear Thomas,

Is your statement about ORDER BY PRIMARY KEY requiring all those key fields in the select list still current?

We are testing on a 7.40 SP9 with HDB 1.00.095 and from our tests the statement looks like working fine and behaves exactly the same as when explicitly specifying the key fields, regardless of whether they are in the select field list.

Simple example:

SELECT landk FROM t005

INTO CORRESPONDING FIELDS OF ls_t005

ORDER BY PRIMARY KEY.

WRITE: / ls_t005-landk.

ENDSELECT.

Returns exactly the same as:

SELECT landk FROM t005

INTO CORRESPONDING FIELDS OF ls_t005

ORDER BY land1.

WRITE: / ls_t005-landk.

ENDSELECT.

It doesn't give a syntax error and also doesn't come up in ATC check.

Best regards,

Pieter

Re: Sorting Order in SQL Queries

thomasgauweiler — Mon, 06 Jul 2015 07:43:03 GMT

Dear Pieter,

I forgot to mention that this rule applies only in the context of a FOR ALL ENTRIES.

A look at the online help shows:

If the addition FOR ALL ENTRIES is used in the WHERE condition, ORDER BY can only be used with the addition PRIMARY KEY and all columns of the primary key (except the client column of client-specific tables) must be specified after SELECT list.

Best Regards,

Thomas

Re: Sorting Order in SQL Queries

Former Member — Thu, 30 Jul 2015 12:37:41 GMT

Thanks Thomas, clear now.

Best regards,

Pieter