Sorting internal table before and after HANA upgrade?

[former_member300568]

Hi ,

I would like to understand how the itabs are sorted before and after HANA upgrade.

I have a select query which joins vrpma, vbpa and vbrk table.

Selects the below fields into an internal table itab

        matnr  TYPE matnr,
        vtweg  TYPE vrpma-vtweg,
        fkart  TYPE vrpma-fkart,
        vbeln  TYPE vbrp-vbeln,
        posnr  TYPE vbrp-posnr,
        fkimg  TYPE vbrp-fkimg,    
        fklmg  TYPE vbrp-fklmg,    
        vrkme  TYPE vbrp-vrkme,
        netwr  TYPE vbrp-netwr,
        kunag  TYPE vbrk-kunag,
        erdat  TYPE vbrk-fkdat

Now in one env which is upgraded to HANA Database, the internal table is sorted by VBELN.

In another environment which is NOT HANA Database , the internal table is sorted by Matnr .

Select Query is below and same in both env

SELECT vbrp~matnr vrpma~vtweg vrpma~fkart
         vbrp~vbeln vbrp~posnr
         vbrp~fkimg vbrp~fklmg
         vbrp~vrkme vbrp~netwr
         vbrk~kunag vbrk~erdat
         vbrk~zuonr 
         vbrk~sfakn 
*         vbrp~aubel 
         vbrk~knumv vbpa~kunnr vbrk~fkdat vbrp~prsdt
         vbrp~zzterr vbrp~pstyv                             
  FROM ( ( ( vrpma JOIN vbrp ON vrpma~vbeln = vbrp~vbeln AND
                            vrpma~posnr = vbrp~posnr )
           JOIN vbrk ON vbrk~vbeln = vrpma~vbeln )
           JOIN vbpa ON vrpma~vbeln = vbpa~vbeln )          
  INTO TABLE i_sales
  FOR ALL ENTRIES IN i_materials
  WHERE vrpma~matnr = i_materials-matnr
  AND vrpma~vkorg = p_vkorg  and so on........

[lbreddemann]

Seriously, this question never gets old, hm?

[FCI · Accepted solution]

Hi Divya,

If you don't specify any ORDER BY in your SELECT, the order of the result set is not predictable. It depends on the database and on the execution plan.

With 2 different DB, it's no suprise that you obtain 2 different sorting orders.

Regards,

Frederic

[former_member300568 · Accepted solution]

Hi Fredric,

So we did remediation on the said HANA upgraded environment and it did not throw an error/warning/message on the select query.

So my question is on which basis did the server decide it should sort on vbeln?.

If you take the above query in any non hana system it will be sorted based on matnr.

I am really intersted to know the logic behind the sorting.

Thanks,

Divya

[FCI · Accepted solution]

If the order is important, you should add an ORDER BY. From the execution plan, you may have this kind of explanation (why is it sorted by VBELN) but as this plan can vary over times, I don't see how this can be relevant.

[horst_keller · Accepted solution]

As documented: Without ORDER BY, the sort order is undefined.

[former_member300568 · Accepted solution]

Thanks Frederic and Horst , But i am still not satisfied/Convinced with the "undefined" answer 🙂 , thought there should be some logic behind the itab sorting .

Thanks anyways.

[pokrakam · Accepted solution]

Undefined does what it says. If you don't define an order, the DB will bring it back in whatever order they're read. It may be sorted one way today because that's the way they were written to disk or because it's choosing a particular index. Tomorrow Basis may do a reorg or adjust some optimisations and it may come back in a different order.

[former_member300568 · Accepted solution]

Thanks ,makes a lot of sense to me.

[matt]

This question has been asked for years. It seems that few people understand that there is no such thing as a sort order on a relational database. It's practically part of the definition.

There is no default order. There is no concept of order. If you want an ordered result set you must use ORDER BY: