Hello @ekekakos ,
You can use CTE's if your abap version is above 7.51. Then you will be able to use the distinct operator on the dates and a cross join with the airlines.
This is also doable without cte's. By fetching the data and looping over each date and every airline. I have included both methods.
CLASS zcl_flights DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
TYPES : BEGIN OF ty_alv,
carrid TYPE /dmo/carrier_id,
fldate TYPE /dmo/flight_date,
name TYPE /dmo/first_name,
END OF ty_alv.
TYPES tt_alv TYPE STANDARD TABLE OF ty_alv WITH EMPTY KEY.
METHODS constructor.
PROTECTED SECTION.
PRIVATE SECTION.
DATA gt_flights TYPE tt_alv.
METHODS get_flights_with_cte
RETURNING VALUE(rt_flights) TYPE tt_alv.
METHODS get_flights_with_abap
RETURNING VALUE(rt_flights) TYPE tt_alv.
ENDCLASS.
CLASS zcl_flights IMPLEMENTATION.
METHOD constructor.
ENDMETHOD.
METHOD get_flights_with_cte.
WITH
+lt_distinct_flight_dates AS (
SELECT DISTINCT
fldate
FROM
zek_sbook
),
+lt_date_carrier_cross_product AS (
SELECT
a~carrid,
d~fldate
FROM
zek_scarr AS a
CROSS JOIN
+lt_distinct_flight_dates AS d
),
+lt_result AS (
SELECT
f~carrid,
f~fldate,
a~name
FROM
+lt_date_carrier_cross_product AS f
LEFT OUTER JOIN
zek_sbook AS a
ON
f~carrid = a~carrid AND
f~fldate = a~fldate
)
SELECT * FROM +lt_result ORDER BY fldate, carrid INTO TABLE @rt_flights.
ENDMETHOD.
METHOD get_flights_with_abap.
SELECT * FROM zek_sbook INTO TABLE @DATA(lt_bookings).
SELECT carrid FROM zek_scarr ORDER BY carrid INTO TABLE @DATA(lt_carriers).
SELECT fldate FROM zek_sbook ORDER BY fldate INTO TABLE @DATA(lt_dates).
DELETE ADJACENT DUPLICATES FROM lt_dates COMPARING fldate.
LOOP AT lt_dates INTO DATA(lv_date).
LOOP AT lt_carriers INTO DATA(lv_carrier).
APPEND VALUE ty_alv(
carrid = lv_carrier
fldate = lv_date
name = COND #(
WHEN line_exists( lt_bookings[ carrid = lv_carrier fldate = lv_date ] )
THEN lt_bookings[ carrid = lv_carrier fldate = lv_date ]-name
)
) TO rt_flights.
ENDLOOP.
ENDLOOP.
ENDMETHOD.
ENDCLASS.
Below is the local test class so you can inject test data and check yourself.
*"* use this source file for your ABAP unit test classes
CLASS ltc_flights DEFINITION FOR TESTING
RISK LEVEL HARMLESS
DURATION SHORT.
PRIVATE SECTION.
DATA mo_cut TYPE REF TO zcl_flights.
CLASS-DATA go_sql_enviroment TYPE REF TO if_osql_test_environment.
CLASS-METHODS class_setup.
CLASS-METHODS class_teardown.
METHODS setup.
METHODS teardown.
METHODS test_flights_with_cte FOR TESTING RAISING cx_static_check.
METHODS test_flights_with_abap FOR TESTING RAISING cx_static_check.
ENDCLASS.
CLASS zcl_flights DEFINITION LOCAL FRIENDS ltc_flights.
CLASS ltc_flights IMPLEMENTATION.
METHOD class_setup.
go_sql_enviroment =
cl_osql_test_environment=>create(
i_dependency_list = VALUE #( ( 'ZEK_SBOOK' ) ( 'ZEK_SCARR' ) )
).
ENDMETHOD.
METHOD class_teardown.
go_sql_enviroment->destroy( ).
ENDMETHOD.
METHOD setup.
mo_cut = NEW #( ).
ENDMETHOD.
METHOD teardown.
go_sql_enviroment->clear_doubles( ).
ENDMETHOD.
METHOD test_flights_with_cte.
DATA lt_expected TYPE zcl_flights=>tt_alv.
DATA lt_bookings TYPE STANDARD TABLE OF zek_sbook.
DATA lt_carriers TYPE STANDARD TABLE OF zek_scarr.
lt_bookings = VALUE #(
( client = 100 carrid = 'AA' fldate = CONV #( '20240530' ) name = 'ELIAS' seats = 5 )
( client = 100 carrid = 'AB' fldate = CONV #( '20240530' ) name = 'MARIA' seats = 2 )
( client = 100 carrid = 'AA' fldate = CONV #( '20240531' ) name = 'JOHN' seats = 3 )
).
lt_carriers = VALUE #(
( client = 100 carrid = 'AA' air_name = 'AIRLINE_1' )
( client = 100 carrid = 'AB' air_name = 'AIRLINE_2' )
( client = 100 carrid = 'AF' air_name = 'AIRLINE_3' )
( client = 100 carrid = 'BE' air_name = 'AIRLINE_4' )
).
go_sql_enviroment->insert_test_data(
EXPORTING
i_data = lt_bookings
).
go_sql_enviroment->insert_test_data(
EXPORTING
i_data = lt_carriers
).
lt_expected = VALUE #(
( carrid = 'AA' fldate = CONV #( '20240530' ) name = 'ELIAS' )
( carrid = 'AB' fldate = CONV #( '20240530' ) name = 'MARIA' )
( carrid = 'AF' fldate = CONV #( '20240530' ) name = '' )
( carrid = 'BE' fldate = CONV #( '20240530' ) name = '' )
( carrid = 'AA' fldate = CONV #( '20240531' ) name = 'JOHN' )
( carrid = 'AB' fldate = CONV #( '20240531' ) name = '' )
( carrid = 'AF' fldate = CONV #( '20240531' ) name = '' )
( carrid = 'BE' fldate = CONV #( '20240531' ) name = '' )
).
cl_abap_unit_assert=>assert_equals(
EXPORTING
act = mo_cut->get_flights_with_cte( )
exp = lt_expected
).
ENDMETHOD.
METHOD test_flights_with_abap.
DATA lt_expected TYPE zcl_flights=>tt_alv.
DATA lt_bookings TYPE STANDARD TABLE OF zek_sbook.
DATA lt_carriers TYPE STANDARD TABLE OF zek_scarr.
lt_bookings = VALUE #(
( client = 100 carrid = 'AA' fldate = CONV #( '20240530' ) name = 'ELIAS' seats = 5 )
( client = 100 carrid = 'AB' fldate = CONV #( '20240530' ) name = 'MARIA' seats = 2 )
( client = 100 carrid = 'AA' fldate = CONV #( '20240531' ) name = 'JOHN' seats = 3 )
).
lt_carriers = VALUE #(
( client = 100 carrid = 'AA' air_name = 'AIRLINE_1' )
( client = 100 carrid = 'AB' air_name = 'AIRLINE_2' )
( client = 100 carrid = 'AF' air_name = 'AIRLINE_3' )
( client = 100 carrid = 'BE' air_name = 'AIRLINE_4' )
).
go_sql_enviroment->insert_test_data(
EXPORTING
i_data = lt_bookings
).
go_sql_enviroment->insert_test_data(
EXPORTING
i_data = lt_carriers
).
lt_expected = VALUE #(
( carrid = 'AA' fldate = CONV #( '20240530' ) name = 'ELIAS' )
( carrid = 'AB' fldate = CONV #( '20240530' ) name = 'MARIA' )
( carrid = 'AF' fldate = CONV #( '20240530' ) name = '' )
( carrid = 'BE' fldate = CONV #( '20240530' ) name = '' )
( carrid = 'AA' fldate = CONV #( '20240531' ) name = 'JOHN' )
( carrid = 'AB' fldate = CONV #( '20240531' ) name = '' )
( carrid = 'AF' fldate = CONV #( '20240531' ) name = '' )
( carrid = 'BE' fldate = CONV #( '20240531' ) name = '' )
).
cl_abap_unit_assert=>assert_equals(
EXPORTING
act = mo_cut->get_flights_with_abap( )
exp = lt_expected
).
ENDMETHOD.
ENDCLASS.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.