How to make ORDER BY case-insensitive?

Question

I'm working with ABAP and OpenSQL and I think I'm running version 7.5, but I'm not really sure about this.

I try to use ORDER BY in my SELECT. My problem is that upper case letters will first shown and than lower case like this:

A B C D E F... a b c d e f - but of course I want it like this: A a B b C c D d E e F f ...

I've tried to to it with ORDER BY UPPER( column2 ) and ORDER BY LOWER( column2 ), but I always get following error (same with lower):

Unknown column name "UPPER( column2 )". until runtime, you cannot specify a field list.

Here is my code:

SELECT * FROM <database table>
  WHERE column1 = @inputParameter
  ORDER BY column2
  INTO CORRESPONDING FIELDS OF TABLE @export_structure

7.5what exactly? There were a lot of interesting additions to OpenSQL in the releases 7.51, 7.52, 7.53 and 7.54. You can check if you connect to the system with SAP Logon and then go to System->Status in the menu bar, click on the button next to "Product version" and check the "Release" of the component "SAP_BASIS". — Philipp, Dec 02 '21 at 10:07
That would be 7.50. Then then the only option appears to be to sort retroactively on the ABAP layer. — Philipp, Dec 02 '21 at 12:29
Oh, and by the way: You might want to tell whoever is responsible for patch management in your organisation to do their job: 7.50 was released 6 years ago. — Philipp, Dec 02 '21 at 12:33

Philipp · Answer 1 · 2021-12-02T12:37:01.077

1

Or you could just sort the results afterwards on the ABAP layer using:

SORT export_structure BY column2 AS TEXT.

The addition AS TEXT does the sorting according to the alphabetic sorting rules for the current locale. Unfortunately it's unavailable in the ORDER BY clause of a SELECT.

This solution should also work fine on very old releases.

edited Dec 02 '21 at 12:37

answered Dec 02 '21 at 11:16

Philipp

67,764
9
118
153

score 0 · Answer 2 · answered Dec 02 '21 at 01:40

0

I dont think the Order by supports functions, even though some dynamic features are available. Docu says it is only limited to the column syntax. Well as of 7.51 at least.

SAP 7.51 Select order by docu

answered Dec 02 '21 at 01:40

phil soady

11,043
5
50
95

score 0 · Answer 3 · answered Dec 02 '21 at 10:26

0

When your system is on Release 7.51 or later, then you can use a WITH ... SELECT.

WITH 
  +tmp AS ( 
    SELECT name_first,
           UPPER( name_first ) AS name_first_upper 
    FROM adrp 
    WHERE persnumber = @inputParameter )
  SELECT name_first FROM +tmp
    ORDER BY name_first_upper
    INTO TABLE @DATA(lt_data).

The WITH-Keyword allows you to define one or more "temporary" SELECTs and then perform a SELECT query on the result-sets of those SELECTs.

answered Dec 02 '21 at 10:26

Philipp

67,764
9
118
153

2

Or to paraphrase a wise poet: *"Yo dawg, I herd you like SELECTs. So I put a SELECT in your SELECT, so you can SELECT while you SELECT."* – Philipp Dec 02 '21 at 10:40

How to make ORDER BY case-insensitive?

3 Answers3