Select query to fetch only two set of data

[sankar1781]

Hi Experts,

My selection input will have a date and level indicator (single) value to fetch the data from the table (as in the attached image). First, I will select all the entries based on the date range. Then, I need to fetch 2 entries of each Trans ID from that, by filtering to pick the preceding entry of the level indicator value and then the entry of the level indicator value.

For example, From the below image, if the selection input is "N" then I need to filter and get the processed data of both K and N.

Please note, K is not always a preceding entry. 

For example from the below one, if the selection input is "V", then 8148 doesn't have any preceding entry, so one record to the final table and 8256 has L, so 2 records to the final table.

All I know, a loop and compare with two internal tables by index to achieve it. But, I want to learn and know, is there a way to achieve it using in line declaration or any other logic to reduce the performance time/issue. 

Thanks.

[Sandra_Rossi]

Do you want us to code it for you, or did you start coding and do you have a precise question?

[SANATKUMAR]

My selection input will have a date and level indicator (single) value to fetch the data from the table (as in the attached image). First, I will select all the entries based on the date range. Then, I need to fetch 2 entries of each Trans ID from that, by filtering to pick the preceding entry of the level indicator value and then the entry of the level indicator value. For example, From the below image, if the selection input is "N" then I need to filter and get the processed data of both K and N.

[SANATKUMAR]

TABLES: YOUR_TABLE. DATA: lt_data TYPE TABLE OF YOUR_TABLE, ls_data TYPE YOUR_TABLE, lv_date_from TYPE DATUM, lv_date_to TYPE DATUM, lv_level_indicator TYPE CHAR1, lt_result TYPE TABLE OF YOUR_TABLE. PARAMETERS: p_date_from TYPE DATUM, p_date_to TYPE DATUM, p_level_indicator TYPE CHAR1. START-OF-SELECTION. lv_date_from = p_date_from. lv_date_to = p_date_to. lv_level_indicator = p_level_indicator. " Fetch data based on date range SELECT * FROM YOUR_TABLE INTO TABLE lt_data WHERE date_field BETWEEN lv_date_from AND lv_date_to. " Iterate through retrieved data LOOP AT lt_data INTO ls_data. " Check if the level indicator matches the specified value IF ls_data-level_indicator = lv_level_indicator. " Fetch the preceding entry for the same Trans ID SELECT SINGLE * FROM YOUR_TABLE INTO ls_data WHERE trans_id = ls_data-trans_id AND date_field < ls_data-date_field AND level_indicator = 'K'. " Assuming 'K' is the preceding level indicator " Append to result table APPEND ls_data TO lt_result. " Append the current entry APPEND ls_data TO lt_result. ENDIF. ENDLOOP. " Display or process the fetched data as required LOOP AT lt_result INTO ls_data. WRITE: / ls_data-field1, ls_data-field2. " Display fields as needed ENDLOOP. ``` Replace `YOUR_TABLE`, `date_field`, `level_indicator`, `trans_id`, and other placeholders with the actual table name and field names from your database schema. Also, make sure to adapt the logic to fit your specific requirements and adjust error handling as needed.

[Edrilan_Berisha · Accepted solution]

Hi,

I did not fully understand your description to be honest. Please update it if possible with proper English.

But from what I understood is: you want to filter on that table for the values with "N" and "K".

Why you don't use simply a SELECT * FROM '<table_name>' INTO TABLE @DATA(itab) WHERE level_indicator = 'K' or level_indicator = 'N'.

Also this sentence here:

"But, I want to learn and know, is there a way to achieve it using in line declaration or any other logic to reduce the performance time/issue."

in line decleration does not mean automatically a better performance. If you believe that less coding means improved performance, you are definitely on a wrong track here. I give you a small example.

Having two loops, one nested within the other one:

LOOP AT <itab> INTO DATA(lt_data)
  LOOP AT <itab2> INTO DATA(lt_data2) WHERE <itab2>-field = <some_value>.
  "some logic here, APPEND or INSERT into some other structure or whatever
  ENDLOOP.
ENDLOOP.

This coding is easier to understand and maintain but it's performance-wise worse then the following for instance:

LOOP AT <itab> INTO DATA(lt_data).
 DO.
 READ TABLE <itab2> INDEX 1.
 IF <itab2>-field = <some_value>.
 "some logic here, APPEND or INSERT or whatever
 ELSE.
  EXIT.
 ENDIF.
 ENDDO.
ENDLOOP.

So you see on my second example, I have more lines of coding and it's definitely not as readable as the first approach. But in this specific case it's more performant. 

The lines of coding do not reflect how your coding behaves with huge set of data. That has to be clear to you.

Best,

Edrilan Berisha

SAP S/4HANA Cloud Financials Development

[SANATKUMAR]

To achieve your task in ABAP, you can follow these steps:

1. Create a selection screen with input fields for date range and level indicator.
2. Retrieve data from the table based on the provided date range.
3. Iterate through the retrieved data and filter out entries with the specified level indicator.
4. For each unique Trans ID, fetch the preceding entry with the specified level indicator and the entry with the selected level indicator.
5. Process and display the fetched data as required.

    programmimg code: 

DATA: lt_data TYPE TABLE OF YOUR_TABLE,
ls_data TYPE YOUR_TABLE,
lv_date_from TYPE DATUM,
lv_date_to TYPE DATUM,
lv_level_indicator TYPE CHAR1,
lt_result TYPE TABLE OF YOUR_TABLE.

PARAMETERS: p_date_from TYPE DATUM,
p_date_to TYPE DATUM,
p_level_indicator TYPE CHAR1.

START-OF-SELECTION.

lv_date_from = p_date_from.
lv_date_to = p_date_to.
lv_level_indicator = p_level_indicator.

" Fetch data based on date range
SELECT * FROM YOUR_TABLE
INTO TABLE lt_data
WHERE date_field BETWEEN lv_date_from AND lv_date_to.

" Iterate through retrieved data
LOOP AT lt_data INTO ls_data.

" Check if the level indicator matches the specified value
IF ls_data-level_indicator = lv_level_indicator.

" Fetch the preceding entry for the same Trans ID
SELECT SINGLE * FROM YOUR_TABLE
INTO ls_data
WHERE trans_id = ls_data-trans_id
AND date_field < ls_data-date_field
AND level_indicator = 'K'. " Assuming 'K' is the preceding level indicator

" Append to result table
APPEND ls_data TO lt_result.

" Append the current entry
APPEND ls_data TO lt_result.

ENDIF.

ENDLOOP.

" Display or process the fetched data as required
LOOP AT lt_result INTO ls_data.
WRITE: / ls_data-field1, ls_data-field2. " Display fields as needed
ENDLOOP.
```

Replace `YOUR_TABLE`, `date_field`, `level_indicator`, `trans_id`, and other placeholders with the actual table name and field names from your database schema. Also, make sure to adapt the logic to fit your specific requirements and adjust error handling as needed.

[sankar1781]

Hi SANATKUMAR,

thanks for the response.

As I said, I know how to determine or achieve it but all I want a simple way than loop or normal ABAP. I expect a inline declaration, if any.

And also, did you read it correctly or not. The preceding value which is dynamic and not constant where I can give and read it. I already sorted order by descending and moved to two different internal tables. 

But trying any helps here to code it using 7.4 or later version.

Thanks anyway.