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

Answer 1

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

Answer 2

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.

Answer 3

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.