Select statement ORDER BY Clause

[former_member355263]

Dears,

i have a problem about sorting.

For example:

       SELECT * UP TO 1 ROWS

       FROM ztable

       INTO CORRESPONDING FIELDS OF TABLE it_table

      WHERE zsifseq LE 2

             ORDER BY zsifseq  DESCENDING  zssequ4 DESCENDING.

Before:(in the table (ztable) )             After

zsifseq   zssequ4                       zifseq         zssequ4

    1           1                                 2                 3  line was selected  into it_table!!!

    1           5                                2                 5 --> why was not this line selected  ?

    1           4                                 2                 4

    1           2                                 2                 2

    1           3                                 2                 1

    2           1                                 .....

    2           2

    2           4

    2           5

    2           3

but i want to select  zifseq   : 2  and  zssequ4 : 5

zifseq and zssequ4 types were defined as char(4).

How can i solve this problem?

Thanks in advange..

[Former Member]

Do you have an index on your table, where at least one of the fields is contained? Have you checked the Select at your DB directly (with native SQL for it)?

Are you sure the data is stored as you show it us? Maybe you have some leading zeros or one of the numbers is at another offset in the field.

For example:

If your entry with 3 is stored as '0003' and the entry with 5 as '   5', the entry with 3 has a higher value because '0' is greater than ' '.

Or the second scenario: 3 -> '3   ', 5 -> '   5' => here also 3 is higher because '3' is greater than ' '

[former_member355263]

thank you for your reply.

My table has no index where containe these fields..Is it must?

[SuhaSaha]

CAGLA ** wrote:

thank you for your reply.

My table has no index where containe these fields..Is it must?

No, it is not a must.

Can you check the data in the DB table as suggested by Armin?

IMO the table definition is not correct. If you wanna store numeric data in the field, why did you define it as CHAR?

[horst_keller]

The code looks OK and it shouldn't be influenced by an index, but I'd rather say it is one of the other reasons that Armin has mentioned. What are the data types of the columns? If they are not INTs, you shouldn't compare with integer (LE 2).

[former_member355263]

The colums data type is Char..

Actually i didnt create that table.

Began of project the table was defined as this.and this table is used other programs also.

so i'm not sure about changing data type.

Is there any other way?

[Former Member]

Have you already checked my other hints? What's the raw content of the field for the affected entries you have mentioned?

[horst_keller]

Use the same type for the ABAP field behind WHERE!

[former_member355263]

i think you might be right.

My data like this:

1                       after only sorting:  9

10                                                8

11                                                2

12                                               13.....

13

8

9

1

2

[Former Member]

Sounds like the numbers are stored left-justified. Do you have only numbers in the fields? Maybe it would be a good thing to change the data types to NUMC instead of CHAR. But if the table is already used in production system be carefully changing the type.

[former_member355263]

Yes i agree..  The fiels has only numbers exist.

And it is used in production system so i dont want to change data type..

i think that if i change the data like this:

from 3 to 0003 and from 12 to 0012 ; it seems problem will be solved.

Thank you very much for your cooperation !

[Jelena_Perfiljeva]

You are selecting one row and only have a condition for one field, so you can pretty much expect any random record pulled from the database that matches the condition. I vaguely recall there were some discussions on SCN about SELECT UP TO... vs. SELECT SINGLE and how one record is selected if the criteria is ambiguous, etc.

Don't remember the details but IMHO it might not be a good idea to rely on DB lottery when you are clearly after particular value. Why not use MAX, for example? ORDER BY merely sorts the results, according to the documentation (unless Horst rewrote it for 7.4 ), so why would it influence the selection? I might be confused though...

[horst_keller]

I've written a blog .

There I say for "Selecting a Partly Specified Row":

With UP TO 1 ROWS you tell the database to pass only one row in its tabular result set and the internal table contains only one line. But be aware, that the returned row is not defined. It can be any of those specified by the partial key. In order to get a defined row in respect to the sort order, you can add an ORDER BY clause.

Therefore, the usage of ORDER BY should be perfectly fine in order to circumvent the "DB lottery".

I'd rather guess that the problem here is comparing character fields with numerical values, as Armin has pointed out.