Sorting Order in SQL Queries

[former_member196555]

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.

[thomasgauweiler]

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

[former_member196555]

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.

[thomasgauweiler]

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

[Former Member]

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

[thomasgauweiler]

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

[Former Member]

Thanks Thomas, clear now.

Best regards,

Pieter