topic Re: Select statement ORDER BY Clause in Application Development and Automation Discussions

Select statement ORDER BY Clause

Former Member — Thu, 25 Aug 2016 11:49:24 GMT

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..

Re: Select statement ORDER BY Clause

Former Member — Thu, 25 Aug 2016 13:36:05 GMT

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 ' '

Re: Select statement ORDER BY Clause

Former Member — Thu, 25 Aug 2016 13:58:26 GMT

thank you for your reply.

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

Re: Select statement ORDER BY Clause

SuhaSaha — Thu, 25 Aug 2016 14:01:27 GMT


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?

Re: Select statement ORDER BY Clause

retired_member — Thu, 25 Aug 2016 14:08:40 GMT

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).

Re: Select statement ORDER BY Clause

Former Member — Thu, 25 Aug 2016 14:34:23 GMT

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?

Re: Select statement ORDER BY Clause

Former Member — Thu, 25 Aug 2016 14:40:02 GMT

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

Re: Select statement ORDER BY Clause

retired_member — Thu, 25 Aug 2016 15:25:31 GMT

Use the same type for the ABAP field behind WHERE!

Re: Select statement ORDER BY Clause

Jelena_Perfiljeva — Thu, 25 Aug 2016 22:00:54 GMT

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...

Re: Select statement ORDER BY Clause

retired_member — Fri, 26 Aug 2016 05:43:36 GMT

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.

Re: Select statement ORDER BY Clause

Former Member — Fri, 26 Aug 2016 07:43:59 GMT

i think you might be right.

My data like this:

1 after only sorting: 9

10 8

11 2

12 13.....

Re: Select statement ORDER BY Clause

Former Member — Fri, 26 Aug 2016 08:15:41 GMT

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.

Re: Select statement ORDER BY Clause

Former Member — Fri, 26 Aug 2016 08:33:28 GMT

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 !