How to sort based on user input values

shashikiran_r — Fri, 24 May 2024 09:30:53 GMT

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.

Re: How to sort based on user input values

Sandra_Rossi — Fri, 24 May 2024 12:53:43 GMT

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.

Re: How to sort based on user input values

Romanos — Wed, 29 May 2024 00:07:34 GMT

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.

Re: How to sort based on user input values

shashikiran_r — Wed, 29 May 2024 04:51:59 GMT

Thanks for your approach

Re: How to sort based on user input values

Sandra_Rossi — Wed, 29 May 2024 06:34:52 GMT

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

Question Re: How to sort based on user input values in Technology Q&A

How to sort based on user input values

Re: How to sort based on user input values

Re: How to sort based on user input values

Re: How to sort based on user input values

Re: How to sort based on user input values