Error while Sorting decending

[Former Member]

I have come across an issue when sorting descending that I can't explain.

I am trying to disiplay an internal table in a particular descending order, however if I sort before I display descending, and then resort without parameters, the values in the table are no longer equal.

The following code is enough to prove my issue.

  int_rec    LIKE zcrd_intrate,
  it_ints    LIKE STANDARD TABLE OF int_rec,
  it_ints2   LIKE STANDARD TABLE OF int_rec.

  SELECT * INTO TABLE it_ints
    FROM zcrd_intrate
    WHERE rectyp = 'OO'.

  it_ints2 = it_ints.

  SORT it_ints2.
  SORT it_ints DESCENDING BY fico_high
                             term_high
                             ltv_high
                             eff_dt_high.
  SORT it_ints.

  IF it_ints NE it_ints2.
{color:red}    WRITE:/ 'We have an issue'.{color}
    PERFORM print_them.
  ELSE.
{color:green}  WRITE:/ 'All''s good'.{color}
    PERFORM print_them.
  ENDIF.

I constantly get the message 'We have an issue'.

I have tried reducing the number of fields from 4, 3 , 2 and finally 1, with no difference.

I have tried using [] after the table name where appropiate all with the same issue.

I also have taken out the DESCENDING BY sort and when that test is run, the result is the 'All''s good' message.

Suggestions are welcome.

Table row is defiened as :

Field Pos Key Data Element Type Len Domain

MANDT 1 X MANDT CLNT 3 MANDT

RECTYP 2 X ZINT_REC_TYP CHAR 2 ZINT_REC_TYP

FICO_HIGH 3 X ZFICO_SCORE NUMC 3 NUM3

FICO_LOW 4 X ZFICO_SCORE NUMC 3 NUM3

TERM_HIGH 5 X Z_TERM_HIGH NUMC 3 Z_TERM

TERM_LOW 6 X Z_TERM_LOW NUMC 3 Z_TERM

LTV_HIGH 7 X ZLTV_D DEC 5 2 ZLTV_D

LTV_LOW 8 X ZLTV_D DEC 5 2 ZLTV_D

EFF_DT_HIGH 9 Z_EFF_DATE_HIGH DATS 8 DATUM

EFF_DT_LOW 10 Z_EFF_DATE_LOW DATS 8 DATUM

INT_RATE 11 ZCRD_RATE DEC 12 3

Moderator Message: Please use more descriptive subject lines while asking your question!

Edited by: Suhas Saha on Jun 20, 2011 5:39 PM

[SuhaSaha]

Hello Paul,

F1 on the SORT keyword reveals the problem,

Sorting is instable by default, which means that the relative order of rows that do not have different sort keys is not retained when they are sorted, and can change when you sort more than once. The addition STABLE can be used for stable sorting.

BR,

Suhas

[Former Member]

Suhas, thanks for the reply. Unfortunetly, this did not help.

This table has 9 key fields after the MANDT field. Only field that is not a key is the interest rate.

I modified the program by adding a delete adjacent duplicates as well as sorting on all 9 key fields.

Same problem resulted.

I additionaly tested by counting records before and after the delete. No rows deleted.

  REFRESH it_ints.
  REFRESH it_ints2.

  SELECT * INTO TABLE it_ints
    FROM zcrd_intrate
    WHERE rectyp = rectyp.

  it_ints2 = it_ints.

**  PERFORM print_them.   NEW-PAGE.

  DELETE ADJACENT DUPLICATES FROM it_ints COMPARING ALL FIELDS.

**  PERFORM print_them.   NEW-PAGE.

  SORT it_ints2.
  SORT it_ints STABLE DESCENDING BY
                             rectyp
                             fico_high
                             fico_low
                             term_high
                             term_low
                             ltv_high
                             ltv_low
                             eff_dt_high
                             eff_dt_low.
  SORT it_ints.

  IF it_ints NE it_ints2.
    WRITE:/ 'We have an issue'.
**    PERFORM print_them.
  ELSE.
    WRITE:/ 'All''s good'.
**    PERFORM print_them.
  ENDIF.

[former_member182670]

Default sort order is ascending.

So

SORT it_ints2.

sorts in ASCENDING order.

[Former Member]

Although you are correct, you will note that after I sort the one table descending, I immediatly sort it which again will sort it ascending prior to the compare.

[former_member182670]

You're right. I missed this part.

Coming back to SATBLE sorting. Did you try to sort stable both tables before comparing?

SORT it_ints2 STABLE.
  SORT it_ints STABLE.

<Added code tags>

Edited by: Suhas Saha on Jun 20, 2011 7:30 PM

[SuhaSaha]

Hello Paul,

I tried with a sample program on SBOOK table & it works OK for me! I understand the inconsistent behaviour(between your program & mine) is because SAP has mentioned multiple SORTs on the same table can result in relative order of rows being changed.

DATA: gt_book     TYPE STANDARD TABLE OF sbook,
      gt_book_dup TYPE STANDARD TABLE OF sbook.

SELECT * FROM sbook
  INTO TABLE gt_book
  WHERE carrid = 'AA'.

gt_book_dup = gt_book.
SORT gt_book_dup STABLE.

SORT gt_book STABLE DESCENDING BY bookid customid luggweight forcuram
order_date.

SORT gt_book STABLE.

IF gt_book NE gt_book_dup.
  WRITE: / 'We have error'.
ELSE.
  WRITE / 'We are OK'.
ENDIF.

Anyway did you try to stabilise each SORT as Tomek has mentioned? If you're using the new debugger you can use the [Diff tool|http://help.sap.com/saphelp_NW70EHP1core/helpdata/en/2c/840b42202cce6ce10000000a155106/content.htm] to analyse the differences!

BR,

Suhas

Edited by: Suhas Saha on Jun 20, 2011 7:31 PM

[Former Member]

Results where the same.

  REFRESH it_ints.
  REFRESH it_ints2.

  SELECT * INTO TABLE it_ints
    FROM zcrd_intrate
    WHERE rectyp = rectyp.

  it_ints2 = it_ints.

**  PERFORM print_them.   NEW-PAGE.

  DELETE ADJACENT DUPLICATES FROM it_ints COMPARING ALL FIELDS.

**  PERFORM print_them.   NEW-PAGE.

  SORT it_ints2 STABLE.
  SORT it_ints STABLE DESCENDING BY
                             rectyp
                             fico_high
                             fico_low
                             term_high
                             term_low
                             ltv_high
                             ltv_low
                             eff_dt_high
                             eff_dt_low.
  SORT it_ints STABLE.

  IF it_ints NE it_ints2.
    WRITE:/ 'We have an issue'.
**    PERFORM print_them.
  ELSE.
    WRITE:/ 'All''s good'.
**    PERFORM print_them.
  ENDIF.

[Former Member]

With this concept, before the descending, I used STABLE on both, and result was data was equal. No Issues.

  REFRESH it_ints.
  REFRESH it_ints2.

  SELECT * INTO TABLE it_ints
    FROM zcrd_intrate
    WHERE rectyp = rectyp.

  it_ints2 = it_ints.

**  PERFORM print_them.   NEW-PAGE.

  DELETE ADJACENT DUPLICATES FROM it_ints COMPARING ALL FIELDS.

**  PERFORM print_them.   NEW-PAGE.

  SORT it_ints2 STABLE.
  SORT it_ints STABLE.

  IF it_ints NE it_ints2.
    WRITE:/ 'We have an issue'.
**    PERFORM print_them.
  ELSE.
    WRITE:/ 'All''s good'.
**    PERFORM print_them.
  ENDIF.

  SORT it_ints STABLE DESCENDING BY
                             rectyp
                             fico_high
                             fico_low
                             term_high
                             term_low
                             ltv_high
                             ltv_low
                             eff_dt_high
                             eff_dt_low.
  SORT it_ints STABLE.

  IF it_ints NE it_ints2.
    WRITE:/ 'We have an issue'.
**    PERFORM print_them.
  ELSE.
    WRITE:/ 'All''s good'.
**    PERFORM print_them.
  ENDIF.

[Former Member]

I don't seem to have DIFF too. I am running on 4.6e.

[SuhaSaha]

Hello Paul,

I'm on release 701 & i suppose i can't replicate your situation! Could you just execute my code snippet & check the output?

Let me just add i didn't seem to have problem without the STABLE addition. In both the cases multiple SORTs did not result in any differences!

AFAIK the new debugger is not available in 4.6E.

BR,

Suhas

[Former Member]

This code ran properly.

REPORT YPTCX_SORT_DESC_ISSUE .

DATA: gt_book     TYPE STANDARD TABLE OF sbook,
      gt_book_dup TYPE STANDARD TABLE OF sbook.

SELECT * FROM sbook
  INTO TABLE gt_book
  WHERE carrid = 'AA'.

gt_book_dup = gt_book.
SORT gt_book_dup STABLE.

SORT gt_book STABLE DESCENDING BY bookid customid luggweight forcuram
order_date.

SORT gt_book STABLE.

IF gt_book NE gt_book_dup.
  WRITE: / 'We have error'.
ELSE.
  WRITE / 'We are OK'.           "<=======  got this message
ENDIF.

[former_member182670]

How many records do you have in it_ints?

Would it be possible to paste the data here?

[Former Member]

162 Records

the 2 LTV and the Int rate field are defined as Dec.

I did email the data to you as well as screen shot of the Table Structure.

Paul

[Former Member]

Paul,

What business req are you trying to achieve by Sorting by a set order and then Sorting again.

Also - I might suggest declaring your tables as SORTED tables with a declared table key - but again, I am unsure of your business req(s).

[Former Member]

I sort with the descending to show the most current row and descention by the other catagories.

The user then can change the data. When they exit, I resort back to the original order and check to see if the data has changed.

I the case listed, without changing data, I'm always getting flagged that the data is changed and thus will ask the user if they want to save it before exiting.

[Former Member]

Paul,

If I follow you, then do this:

1) SELECT the data from the DB into a local table - as you are currently

2) Sort the data in that local table (calling it CURRent - for now) and include ALL fields in that SORT

3) Copy that now sorted CURR table into an ORIGinal table - as you are currently

4) Allow the user to "play with" the CURR table

5) At time of save, take the CURR table (which may have been changed) and sort it again using the same sort order declared in Step 2 (all fields).

6) Now do "IF it_Curr[ ] NE it_ORIG[ ]" --> then changes were made, so expose the save changes pop-up

Also - if you are using an ALV grid for the CURR table, you can look it Method CHECK_CHANGED_DATA in the ALV Grid class.

[Former Member]

The only differences that I see with your suggestions are:

Data would both be sorted in descending prior to "user play time"

Upon return would sort again descending

Compare based on both tables descending.

Additionally, you mention sorting with just the data fields.

The only additional fields would be MANDT and the create date/time/user as well as the Change date/time/user.

Although I can see the work around using this method, it doesn't provide me with the confidence knowing that the base sort of both tables should be identical when no data is changed.

The code I have been showing is a snipet of the total program in a work program to isolate the issue and know why this is happening.

[Former Member]

Paul,

In your sample code, the base sorts are not the same.

You are sorting one of the table twice - and not using all of the fields in the sort. Without a well defined key for uniqueness, ABAP (or any prog lang) can not guarantee the same sort order. The same sort order is only guaranteed when all fields (data or key fields) are included. In your case, using ALL of the fields in an ASCENDING/DESCENDING BY clause.

[former_member182670]

Agree with John.

You should either sort using all fields (at least all which are part of the db table key) or declare the internal tables specifying the key explicitly.

When you declare

it_ints LIKE STANDARD TABLE OF int_rec

system creates default key consisting of all character-like fields which is different from your db key as it doesn't include LTV_HIGH and LTV_LOW which are DEC.

Statement

SORT it_ints.

uses this default key and that's why you get these unpredictable results.

So to summarize:

- either declare it_ints and it_ints2 specifying key fields (same fields as DB key)

- or use these fields explicitly when sorting instead of just 'SORT it_ints'

[Former Member]

Tomek,

Wonderfully expressed.

[Former Member]

Ok.. now I understand the direction and reasoning.

I did make the changes where suggested as follows and all worked find.

Thanks for all your help.

  SORT it_ints2 STABLE ASCENDING BY
                             rectyp
                             fico_high
                             fico_low
                             term_high
                             term_low
                             ltv_high
                             ltv_low
                             eff_dt_high
                             eff_dt_low.
  SORT it_ints STABLE ASCENDING BY
                             rectyp
                             fico_high
                             fico_low
                             term_high
                             term_low
                             ltv_high
                             ltv_low
                             eff_dt_high
                             eff_dt_low.

  SORT it_ints STABLE DESCENDING BY
                             rectyp
                             fico_high
                             fico_low
                             term_high
                             term_low
                             ltv_high
                             ltv_low
                             eff_dt_high
                             eff_dt_low.
  SORT it_ints STABLE ASCENDING BY
                             rectyp
                             fico_high
                             fico_low
                             term_high
                             term_low
                             ltv_high
                             ltv_low
                             eff_dt_high
                             eff_dt_low.