ABAP SQL Features 2024/2025: Overview of the Most Important Updates

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Organization Hierarchy'
define hierarchy ZI_OrgHierarchy
  as parent child hierarchy(
    source ZI_Organization
    child to parent association _ParentOrg
    start where ParentOrgId is initial
    siblings order by OrgName
  )
{
  key OrgId,
      OrgName,
      ParentOrgId,
      OrgLevel,
      Manager,
      _ParentOrg
}

CLASS zcl_hierarchy_demo DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

CLASS zcl_hierarchy_demo IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " Hierarchy functions in ABAP SQL
    SELECT FROM HIERARCHY(
             SOURCE zi_organization
             CHILD TO PARENT ASSOCIATION _parentorg
             START WHERE parentorgid IS INITIAL
             SIBLINGS ORDER BY orgname
           ) AS h
      FIELDS
        orgid,
        orgname,
        parentorgid,
        " Hierarchy-specific functions
        HIERARCHY_LEVEL AS level,
        HIERARCHY_RANK AS rank,
        HIERARCHY_TREE_SIZE AS subtree_size,
        HIERARCHY_PARENT_RANK AS parent_rank,
        HIERARCHY_IS_ORPHAN AS is_orphan,
        HIERARCHY_IS_CYCLE AS is_cycle
      INTO TABLE @DATA(lt_hierarchy).

    out->write( '=== Organization Hierarchy ===' ).
    LOOP AT lt_hierarchy INTO DATA(ls_org).
      " Indentation based on level
      DATA(lv_indent) = repeat( val = '  ' occ = ls_org-level ).
      out->write( |{ lv_indent }{ ls_org-orgname } (Level { ls_org-level })| ).
    ENDLOOP.

    " Query only specific levels
    SELECT FROM HIERARCHY(
             SOURCE zi_organization
             CHILD TO PARENT ASSOCIATION _parentorg
             START WHERE parentorgid IS INITIAL
           ) AS h
      FIELDS orgid, orgname, HIERARCHY_LEVEL AS level
      WHERE HIERARCHY_LEVEL <= 2  " Only top 2 levels
      INTO TABLE @DATA(lt_top_levels).

    out->write( '' ).
    out->write( '=== Top 2 Levels ===' ).
    LOOP AT lt_top_levels INTO DATA(ls_top).
      out->write( |Level { ls_top-level }: { ls_top-orgname }| ).
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

" Employee count per organizational unit including subunits
SELECT FROM HIERARCHY(
         SOURCE zi_org_employees
         CHILD TO PARENT ASSOCIATION _parentorg
         START WHERE parentorgid IS INITIAL
         AGGREGATING employee_count WITH SUM AS total_employees
       ) AS h
  FIELDS
    orgid,
    orgname,
    employee_count,      " Direct employees
    total_employees      " Including all subunits
  INTO TABLE @DATA(lt_emp_count).

CLASS zcl_median_demo DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

CLASS zcl_median_demo IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " Median salary per department
    SELECT department_id,
           AVG( salary ) AS avg_salary,
           MEDIAN( salary ) AS median_salary,
           MIN( salary ) AS min_salary,
           MAX( salary ) AS max_salary,
           COUNT( * ) AS employee_count
      FROM zemployees
      GROUP BY department_id
      INTO TABLE @DATA(lt_stats).

    out->write( '=== Salary Statistics per Department ===' ).
    LOOP AT lt_stats INTO DATA(ls_stat).
      out->write( |Department { ls_stat-department_id }:| ).
      out->write( |  Average: { ls_stat-avg_salary DECIMALS = 2 }| ).
      out->write( |  Median:  { ls_stat-median_salary DECIMALS = 2 }| ).
      out->write( |  Range:   { ls_stat-min_salary } - { ls_stat-max_salary }| ).
      out->write( '' ).
    ENDLOOP.

    " Comparison: Median vs. Average shows distribution
    " Median < Average = many low values, few high values
    " Median > Average = many high values, few low values
  ENDMETHOD.

ENDCLASS.

" Calculate salary percentiles
SELECT department_id,
       " Continuous percentile (interpolated)
       PERCENTILE_CONT( 0.25 ) WITHIN GROUP ( ORDER BY salary )
         AS percentile_25,
       PERCENTILE_CONT( 0.50 ) WITHIN GROUP ( ORDER BY salary )
         AS percentile_50,
       PERCENTILE_CONT( 0.75 ) WITHIN GROUP ( ORDER BY salary )
         AS percentile_75,
       PERCENTILE_CONT( 0.90 ) WITHIN GROUP ( ORDER BY salary )
         AS percentile_90,
       " Discrete percentile (nearest actual value)
       PERCENTILE_DISC( 0.50 ) WITHIN GROUP ( ORDER BY salary )
         AS median_discrete
  FROM zemployees
  GROUP BY department_id
  INTO TABLE @DATA(lt_percentiles).

" Application: Define salary classes
" < 25%: Entry level
" 25-50%: Junior
" 50-75%: Senior
" > 75%: Expert/Lead

CLASS zcl_string_agg_demo DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

CLASS zcl_string_agg_demo IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " All employee names per department as string
    SELECT department_id,
           STRING_AGG( employee_name, ', ' ORDER BY employee_name )
             AS all_employees,
           COUNT( * ) AS count
      FROM zemployees
      GROUP BY department_id
      INTO TABLE @DATA(lt_depts).

    LOOP AT lt_depts INTO DATA(ls_dept).
      out->write( |Department { ls_dept-department_id } ({ ls_dept-count } employees):| ).
      out->write( |  { ls_dept-all_employees }| ).
      out->write( '' ).
    ENDLOOP.

    " Merge article tags
    SELECT article_id,
           title,
           STRING_AGG( tag, ' | ' ORDER BY tag ) AS tags
      FROM zarticles
      INNER JOIN zarticle_tags ON zarticle_tags~article_id = zarticles~article_id
      GROUP BY zarticles~article_id, title
      INTO TABLE @DATA(lt_articles).

    out->write( '=== Articles with Tags ===' ).
    LOOP AT lt_articles INTO DATA(ls_art).
      out->write( |{ ls_art-title }| ).
      out->write( |  Tags: { ls_art-tags }| ).
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

CLASS zcl_grouping_sets_demo DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

CLASS zcl_grouping_sets_demo IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " Revenue by multiple dimensions simultaneously
    SELECT
        region,
        product_category,
        SUM( amount ) AS total_sales,
        COUNT( * ) AS order_count,
        " GROUPING() shows whether column was aggregated (1) or not (0)
        GROUPING( region ) AS region_grouped,
        GROUPING( product_category ) AS category_grouped
      FROM zsales_orders
      GROUP BY GROUPING SETS (
        ( region, product_category ),  " Per region and category
        ( region ),                     " Only per region
        ( product_category ),           " Only per category
        ( )                             " Grand total
      )
      ORDER BY region, product_category
      INTO TABLE @DATA(lt_sales).

    out->write( '=== Revenue Analysis (GROUPING SETS) ===' ).
    LOOP AT lt_sales INTO DATA(ls_sale).
      CASE ls_sale-region_grouped.
        WHEN 1.
          " Region aggregated
          CASE ls_sale-category_grouped.
            WHEN 1.
              " Both aggregated = grand total
              out->write( |TOTAL: { ls_sale-total_sales }| ).
            WHEN 0.
              " Only per category
              out->write( |  Category { ls_sale-product_category }: { ls_sale-total_sales }| ).
          ENDCASE.
        WHEN 0.
          " Region not aggregated
          CASE ls_sale-category_grouped.
            WHEN 1.
              " Only per region
              out->write( |Region { ls_sale-region }: { ls_sale-total_sales }| ).
            WHEN 0.
              " Per region and category
              out->write( |  { ls_sale-region } - { ls_sale-product_category }: { ls_sale-total_sales }| ).
          ENDCASE.
      ENDCASE.
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

" Revenue with subtotals: Region -> Category -> Product
SELECT
    region,
    product_category,
    product_name,
    SUM( amount ) AS total_sales
  FROM zsales_orders
  GROUP BY ROLLUP ( region, product_category, product_name )
  ORDER BY region, product_category, product_name
  INTO TABLE @DATA(lt_rollup).

" Result:
" NORTH | Electronics | Laptop   | 5000   (Product)
" NORTH | Electronics | Monitor  | 2000   (Product)
" NORTH | Electronics | NULL     | 7000   (Category sum)
" NORTH | Software    | Office   | 1500   (Product)
" NORTH | Software    | NULL     | 1500   (Category sum)
" NORTH | NULL        | NULL     | 8500   (Region sum)
" NULL  | NULL        | NULL     | 15000  (Grand total)

" Revenue for all combinations of region and category
SELECT
    region,
    product_category,
    SUM( amount ) AS total_sales,
    GROUPING( region ) AS region_agg,
    GROUPING( product_category ) AS category_agg
  FROM zsales_orders
  GROUP BY CUBE ( region, product_category )
  INTO TABLE @DATA(lt_cube).

" Result contains:
" - Per region and category
" - Only per region (all categories)
" - Only per category (all regions)
" - Grand total

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Filtered Orders'
define view entity ZI_OrdersFiltered
  with parameters
    @Environment.systemField: #SYSTEM_DATE
    p_date      : abap.dats,
    @Consumption.defaultValue: 'OPEN'
    p_status    : abap.char(10),
    @Consumption.defaultValue: '100'
    p_min_amount: abap.dec(15,2)
  as select from zorders
{
  key order_id,
      customer_id,
      order_date,
      status,
      amount,
      currency
}
where order_date >= $parameters.p_date
  and status     =  $parameters.p_status
  and amount     >= $parameters.p_min_amount

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.allowExtensions: true
define view entity ZC_OrderAnalysis
  as projection on ZI_Orders
{
  @UI.lineItem: [{ position: 10 }]
  @UI.selectionField: [{ position: 10 }]
  key OrderId,

  @UI.lineItem: [{ position: 20 }]
  @Consumption.filter.selectionType: #INTERVAL
  OrderDate,

  @UI.lineItem: [{ position: 30 }]
  @Semantics.amount.currencyCode: 'Currency'
  Amount,

  @Consumption.valueHelpDefinition: [{ entity: { name: 'I_Currency', element: 'Currency' } }]
  Currency,

  " Calculated field with annotation inheritance
  @Aggregation.default: #SUM
  _Items.TotalQuantity as TotalQuantity
}

define view entity ZC_SalesOrder
  as projection on ZI_SalesOrder
{
  key SalesOrderId,
      CustomerName,
      OrderDate,

      " Redirect to different view for items
      @ObjectModel.compositionReference: true
      _Items : redirected to composition child ZC_SalesOrderItem
}

define view entity ZC_SalesOrderItem
  as projection on ZI_SalesOrderItem
{
  key SalesOrderId,
  key ItemNumber,
      Product,
      Quantity,

      _Header : redirected to parent ZC_SalesOrder
}

SELECT
    " Remove leading zeros
    LTRIM( document_number, '0' ) AS clean_doc_num,

    " Remove trailing whitespace and special characters
    RTRIM( description, ' .-_' ) AS clean_description,

    " Combined: Remove leading/trailing characters
    RTRIM( LTRIM( code, '0' ), '0' ) AS clean_code
  FROM zdocuments
  INTO TABLE @DATA(lt_docs).

SELECT
    " Pad material number to 10 digits with leading zeros
    LPAD( material_id, 10, '0' ) AS material_id_padded,

    " Pad description to fixed width
    RPAD( description, 40, ' ' ) AS description_fixed
  FROM zmaterials
  INTO TABLE @DATA(lt_mats).

SELECT
    email,
    " Find position of @
    INSTR( email, '@' ) AS at_position,
    " Extract domain
    SUBSTRING( email, INSTR( email, '@' ) + 1 ) AS domain
  FROM zusers
  WHERE INSTR( email, '@' ) > 0
  INTO TABLE @DATA(lt_emails).

SELECT
    order_id,
    amount,
    order_date,
    CASE
      " Time-based classification
      WHEN DATS_DAYS_BETWEEN( order_date, $session.system_date ) <= 7
        THEN 'This Week'
      WHEN DATS_DAYS_BETWEEN( order_date, $session.system_date ) <= 30
        THEN 'This Month'
      WHEN DATS_DAYS_BETWEEN( order_date, $session.system_date ) <= 90
        THEN 'This Quarter'
      ELSE 'Older'
    END AS age_category,

    CASE
      " Value-based classification with ranges
      WHEN amount < 100 THEN 'Small'
      WHEN amount BETWEEN 100 AND 999 THEN 'Medium'
      WHEN amount BETWEEN 1000 AND 9999 THEN 'Large'
      ELSE 'Enterprise'
    END AS size_category
  FROM zorders
  INTO TABLE @DATA(lt_orders).

SELECT
    customer_id,
    " First non-empty alternative
    COALESCE( phone_mobile, phone_office, phone_home, 'No number' )
      AS contact_phone,

    " Treat empty strings as NULL
    COALESCE( NULLIF( email, '' ), '[email protected]' ) AS email_safe,

    " Prevent division by zero
    CASE
      WHEN NULLIF( total_orders, 0 ) IS NULL THEN 0
      ELSE total_revenue / total_orders
    END AS avg_order_value
  FROM zcustomers
  INTO TABLE @DATA(lt_customers).

CLASS zcl_sales_dashboard_2025 DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

CLASS zcl_sales_dashboard_2025 IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " Dashboard data with new SQL features
    SELECT
        region,
        product_category,
        CAST( order_date AS CHAR( 7 ) ) AS month,  " YYYY-MM

        " Basic aggregations
        COUNT( * ) AS order_count,
        SUM( amount ) AS total_sales,
        AVG( amount ) AS avg_order_value,

        " New aggregate functions
        MEDIAN( amount ) AS median_order_value,
        STRING_AGG( DISTINCT salesperson, ', ' ORDER BY salesperson )
          AS active_salespeople,

        " Percentiles for segmentation
        PERCENTILE_CONT( 0.25 ) WITHIN GROUP ( ORDER BY amount )
          AS q1_threshold,
        PERCENTILE_CONT( 0.75 ) WITHIN GROUP ( ORDER BY amount )
          AS q3_threshold

      FROM zsales_orders
      WHERE order_date >= '20240101'
      GROUP BY region, product_category, CAST( order_date AS CHAR( 7 ) )
      ORDER BY region, product_category, month
      INTO TABLE @DATA(lt_dashboard).

    " Output
    out->write( '=== Sales Dashboard 2024/2025 ===' ).
    out->write( '' ).

    DATA(lv_current_region) = VALUE #( lt_dashboard[ 1 ]-region OPTIONAL ).

    LOOP AT lt_dashboard INTO DATA(ls_row).
      IF ls_row-region <> lv_current_region.
        out->write( '' ).
        out->write( |=== Region: { ls_row-region } ===| ).
        lv_current_region = ls_row-region.
      ENDIF.

      out->write( |{ ls_row-product_category } ({ ls_row-month }):| ).
      out->write( |  Orders: { ls_row-order_count }, | &&
                  |Total: { ls_row-total_sales DECIMALS = 2 }| ).
      out->write( |  Avg: { ls_row-avg_order_value DECIMALS = 2 }, | &&
                  |Median: { ls_row-median_order_value DECIMALS = 2 }| ).
      out->write( |  Q1: { ls_row-q1_threshold DECIMALS = 2 }, | &&
                  |Q3: { ls_row-q3_threshold DECIMALS = 2 }| ).
      out->write( |  Salespeople: { ls_row-active_salespeople }| ).
    ENDLOOP.

    " Summary with ROLLUP
    out->write( '' ).
    out->write( '=== Summary (ROLLUP) ===' ).

    SELECT
        region,
        product_category,
        SUM( amount ) AS total_sales,
        COUNT( * ) AS orders
      FROM zsales_orders
      WHERE order_date >= '20240101'
      GROUP BY ROLLUP ( region, product_category )
      ORDER BY region, product_category
      INTO TABLE @DATA(lt_summary).

    LOOP AT lt_summary INTO DATA(ls_sum).
      CASE abap_true.
        WHEN xsdbool( ls_sum-region IS INITIAL AND ls_sum-product_category IS INITIAL ).
          out->write( |TOTAL: { ls_sum-total_sales } ({ ls_sum-orders } Orders)| ).
        WHEN xsdbool( ls_sum-product_category IS INITIAL ).
          out->write( |  Region { ls_sum-region }: { ls_sum-total_sales }| ).
        WHEN xsdbool( ls_sum-region IS NOT INITIAL ).
          out->write( |    { ls_sum-product_category }: { ls_sum-total_sales }| ).
      ENDCASE.
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

" OLD: Multiple queries
SELECT region, SUM( amount ) FROM zsales GROUP BY region INTO TABLE @DATA(lt_by_region).
SELECT product, SUM( amount ) FROM zsales GROUP BY product INTO TABLE @DATA(lt_by_product).
SELECT SUM( amount ) FROM zsales INTO @DATA(lv_total).

" NEW: Single query
SELECT region, product,
       SUM( amount ) AS total,
       GROUPING( region ) AS r_grp,
       GROUPING( product ) AS p_grp
  FROM zsales
  GROUP BY GROUPING SETS (
    ( region ),
    ( product ),
    ( )
  )
  INTO TABLE @DATA(lt_all).