How to sort based on user input values

[shashikiran_r]

Hello All,

How to sort the data based on the user input values in the selection screen

for example below is the data in the database table TABLE T1

Column 1	Column 2	Column 3
1	B	z
2	B	y
3	C	y
4	A	z
5	D	x
6	C	x
7	A	y
8	B	y
9	D	z
10	A	z

user input for sorting the data

Column2	Column3
B	y
A	z
D	x
C

Expected output.

Column1	Column2	Column3
2	B	y
8	B	y
1	B	z
7	A	y
4	A	z
10	A	z
9	D	z
5	D	x
3	C	y
6	C	x

How this can be achieved in SQL ?

How sorting can be achieved if the data is available in the internal table ?

Regards,

Shashikiran.

[Sandra_Rossi]

Nowhere you indicate the order so you can't sort. Add a column in a table to indicate the order (e.g. B = order 1, A = order 2, D = order 3, C = order 4) and do the join + ORDER BY.

If you have HANA, you should be able to join an internal table.

If not, there are lots of solutions: read and sort in memory, store the internal table into a database table and join + ORDER BY, etc.

[shashikiran_r]

Thanks for your approach

[Romanos · Accepted solution]

You can approach this the same way in SQL as in ABAP. Create a temporary table, loop over the conditions, read the data with the conditions as keys and append the data to the temp table. Note that there should be a more efficient way using joins etc.

CLASS zcl_application DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    TYPES :
      ty_col_1 TYPE i,
      ty_col_2 TYPE c LENGTH 1,
      ty_col_3 TYPE c LENGTH 1.

    TYPES : BEGIN OF ty_data,
              col_1 TYPE ty_col_1,
              col_2 TYPE ty_col_2,
              col_3 TYPE ty_col_3,
            END OF ty_data.

    TYPES tt_data TYPE STANDARD TABLE OF ty_data WITH KEY col_1.

    TYPES : BEGIN OF ty_user_input,
              col_2 TYPE ty_col_2,
              col_3 TYPE ty_col_3,
            END OF ty_user_input.

    TYPES tt_user_input TYPE STANDARD TABLE OF ty_user_input WITH KEY col_2.

    INTERFACES if_amdp_marker_hdb.

    CLASS-METHODS sort
*      AMDP OPTIONS CDS SESSION CLIENT current
      IMPORTING
                VALUE(it_unsorted_table) TYPE tt_data
                VALUE(it_user_input)     TYPE tt_user_input
      EXPORTING VALUE(et_sorted_data)    TYPE tt_data.

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.
CLASS zcl_application IMPLEMENTATION.

  METHOD sort BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.

    declare lt_result, lt_stack table like :et_sorted_data;
    declare i, j, lv_cond_lines_1, lv_cond_lines_2 integer;
    declare lv_condition_col_2, lv_condition_col_3  nvarchar( 1 );

    lt_conditions_col_2 = SELECT col_2 FROM :it_user_input;
    lv_cond_lines_1 = ::ROWCOUNT;
    lt_conditions_col_3 = SELECT col_3 FROM :it_user_input WHERE col_3 <> '';
    lv_cond_lines_2 = ::ROWCOUNT;

    /* DML enabled data as a temp stack to handle duplication */
    INSERT INTO :lt_stack SELECT * FROM :IT_UNSORTED_TABLE;

    FOR i IN 1..LV_COND_LINES_1 DO
        FOR j IN 1..LV_COND_LINES_2 DO

            lv_condition_col_2 = :lt_conditions_col_2.col_2[ i ];
            lv_condition_col_3 = :lt_conditions_col_3.col_3[ j ];

            INSERT INTO :lt_result (
                    SELECT *
                    FROM
                        :lt_stack
                    WHERE
                        col_2 = lv_condition_col_2 and
                        col_3 = lv_condition_col_3
                 );

             DELETE FROM :lt_stack
             WHERE
                    col_2 = lv_condition_col_2 and
                    col_3 = lv_condition_col_3;

        END FOR;
    END FOR;

    et_sorted_data = select * from :lt_result;
  ENDMETHOD.
ENDCLASS.

CLASS ltc_application DEFINITION FOR TESTING
  RISK LEVEL HARMLESS
  DURATION SHORT.

  PRIVATE SECTION.

    METHODS test  FOR TESTING RAISING cx_static_check.

ENDCLASS.


CLASS ltc_application IMPLEMENTATION.

  METHOD test.

    DATA lt_user_input TYPE zcl_application=>tt_user_input.
    DATA lt_unsorted_table TYPE zcl_application=>tt_data.
    DATA lt_expected TYPE zcl_application=>tt_data.
    DATA lt_actual TYPE zcl_application=>tt_data.

    lt_user_input = VALUE #(
        ( col_2 = 'B' col_3 = 'y' )
        ( col_2 = 'A' col_3 = 'z' )
        ( col_2 = 'D' col_3 = 'x' )
        ( col_2 = 'C' col_3 = '' )
    ).

    lt_unsorted_table = VALUE #(
        ( col_1 = 1  col_2 = 'B' col_3 = 'z' )
        ( col_1 = 2  col_2 = 'B' col_3 = 'y' )
        ( col_1 = 3  col_2 = 'C' col_3 = 'y' )
        ( col_1 = 4  col_2 = 'A' col_3 = 'z' )
        ( col_1 = 5  col_2 = 'D' col_3 = 'x' )
        ( col_1 = 6  col_2 = 'C' col_3 = 'x' )
        ( col_1 = 7  col_2 = 'A' col_3 = 'y' )
        ( col_1 = 8  col_2 = 'B' col_3 = 'y' )
        ( col_1 = 9  col_2 = 'D' col_3 = 'z' )
        ( col_1 = 10 col_2 = 'A' col_3 = 'z' )
    ).

    lt_expected = VALUE #(
        ( col_1 = 2  col_2 = 'B' col_3 = 'y' )
        ( col_1 = 8  col_2 = 'B' col_3 = 'y' )
        ( col_1 = 1  col_2 = 'B' col_3 = 'z' )
        ( col_1 = 7  col_2 = 'A' col_3 = 'y' )
        ( col_1 = 4  col_2 = 'A' col_3 = 'z' )
        ( col_1 = 10 col_2 = 'A' col_3 = 'z' )
        ( col_1 = 9  col_2 = 'D' col_3 = 'z' )
        ( col_1 = 5  col_2 = 'D' col_3 = 'x' )
        ( col_1 = 3  col_2 = 'C' col_3 = 'y' )
        ( col_1 = 6  col_2 = 'C' col_3 = 'x' )
    ).

    zcl_application=>sort(
      EXPORTING
        it_unsorted_table = lt_unsorted_table
        it_user_input     = lt_user_input
      IMPORTING
        et_sorted_data    = lt_actual
    ).

    cl_abap_unit_assert=>assert_equals(
      EXPORTING
        act = lt_actual
        exp = lt_expected
    ).
  ENDMETHOD.
ENDCLASS.

[Sandra_Rossi · Accepted solution]

@shashikiran_r The answer is about HANA SQL (SQLScript), but was your question about ABAP SQL or HANA SQL or other database SQL? You only mention ABAP in the tag of your question.