ABAP SQL Neuerungen 2024/2025: Die wichtigsten Features im Überblick

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Organisationshierarchie'
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.
    " Hierarchie-Funktionen 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,
        " Hierarchie-spezifische Funktionen
        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( '=== Organisationshierarchie ===' ).
    LOOP AT lt_hierarchy INTO DATA(ls_org).
      " Einrückung basierend auf Level
      DATA(lv_indent) = repeat( val = '  ' occ = ls_org-level ).
      out->write( |{ lv_indent }{ ls_org-orgname } (Level { ls_org-level })| ).
    ENDLOOP.

    " Nur bestimmte Ebenen abfragen
    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  " Nur Top-2-Ebenen
      INTO TABLE @DATA(lt_top_levels).

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

ENDCLASS.

" Mitarbeiteranzahl pro Organisationseinheit inkl. Untereinheiten
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,      " Direkte Mitarbeiter
    total_employees      " Inkl. aller Untereinheiten
  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-Gehalt pro Abteilung
    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( '=== Gehaltsstatistik pro Abteilung ===' ).
    LOOP AT lt_stats INTO DATA(ls_stat).
      out->write( |Abteilung { ls_stat-department_id }:| ).
      out->write( |  Durchschnitt: { ls_stat-avg_salary DECIMALS = 2 }| ).
      out->write( |  Median:       { ls_stat-median_salary DECIMALS = 2 }| ).
      out->write( |  Spanne:       { ls_stat-min_salary } - { ls_stat-max_salary }| ).
      out->write( '' ).
    ENDLOOP.

    " Vergleich: Median vs. Durchschnitt zeigt Verteilung
    " Median < Durchschnitt = viele niedrige, wenige hohe Werte
    " Median > Durchschnitt = viele hohe, wenige niedrige Werte
  ENDMETHOD.

ENDCLASS.

" Gehaltsperzentile berechnen
SELECT department_id,
       " Kontinuierliches Perzentil (interpoliert)
       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,
       " Diskretes Perzentil (nächster tatsächlicher Wert)
       PERCENTILE_DISC( 0.50 ) WITHIN GROUP ( ORDER BY salary )
         AS median_discrete
  FROM zemployees
  GROUP BY department_id
  INTO TABLE @DATA(lt_percentiles).

" Anwendung: Gehaltsklassen definieren
" < 25%: Einstieg
" 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.
    " Alle Mitarbeiternamen pro Abteilung als 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( |Abteilung { ls_dept-department_id } ({ ls_dept-count } Mitarbeiter):| ).
      out->write( |  { ls_dept-all_employees }| ).
      out->write( '' ).
    ENDLOOP.

    " Tags eines Artikels zusammenführen
    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( '=== Artikel mit 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.
    " Umsatz nach verschiedenen Dimensionen gleichzeitig
    SELECT
        region,
        product_category,
        SUM( amount ) AS total_sales,
        COUNT( * ) AS order_count,
        " GROUPING() zeigt, ob Spalte aggregiert wurde (1) oder nicht (0)
        GROUPING( region ) AS region_grouped,
        GROUPING( product_category ) AS category_grouped
      FROM zsales_orders
      GROUP BY GROUPING SETS (
        ( region, product_category ),  " Pro Region und Kategorie
        ( region ),                     " Nur pro Region
        ( product_category ),           " Nur pro Kategorie
        ( )                             " Gesamtsumme
      )
      ORDER BY region, product_category
      INTO TABLE @DATA(lt_sales).

    out->write( '=== Umsatzanalyse (GROUPING SETS) ===' ).
    LOOP AT lt_sales INTO DATA(ls_sale).
      CASE ls_sale-region_grouped.
        WHEN 1.
          " Region aggregiert
          CASE ls_sale-category_grouped.
            WHEN 1.
              " Beide aggregiert = Gesamtsumme
              out->write( |GESAMT: { ls_sale-total_sales }| ).
            WHEN 0.
              " Nur pro Kategorie
              out->write( |  Kategorie { ls_sale-product_category }: { ls_sale-total_sales }| ).
          ENDCASE.
        WHEN 0.
          " Region nicht aggregiert
          CASE ls_sale-category_grouped.
            WHEN 1.
              " Nur pro Region
              out->write( |Region { ls_sale-region }: { ls_sale-total_sales }| ).
            WHEN 0.
              " Pro Region und Kategorie
              out->write( |  { ls_sale-region } - { ls_sale-product_category }: { ls_sale-total_sales }| ).
          ENDCASE.
      ENDCASE.
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

" Umsatz mit Zwischensummen: Region -> Kategorie -> Produkt
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).

" Ergebnis:
" NORD | Elektronik | Laptop   | 5000   (Produkt)
" NORD | Elektronik | Monitor  | 2000   (Produkt)
" NORD | Elektronik | NULL     | 7000   (Kategorie-Summe)
" NORD | Software   | Office   | 1500   (Produkt)
" NORD | Software   | NULL     | 1500   (Kategorie-Summe)
" NORD | NULL       | NULL     | 8500   (Region-Summe)
" NULL | NULL       | NULL     | 15000  (Gesamtsumme)

" Umsatz für alle Kombinationen von Region und Kategorie
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).

" Ergebnis enthält:
" - Pro Region und Kategorie
" - Nur pro Region (alle Kategorien)
" - Nur pro Kategorie (alle Regionen)
" - Gesamtsumme

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Bestellungen mit Filter'
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,

  " Berechnetes Feld mit Annotation-Vererbung
  @Aggregation.default: #SUM
  _Items.TotalQuantity as TotalQuantity
}

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

      " Redirect zu anderem View für 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
    " Führende Nullen entfernen
    LTRIM( document_number, '0' ) AS clean_doc_num,

    " Trailing Whitespace und Sonderzeichen entfernen
    RTRIM( description, ' .-_' ) AS clean_description,

    " Kombiniert: Führende/Folgende Zeichen entfernen
    RTRIM( LTRIM( code, '0' ), '0' ) AS clean_code
  FROM zdocuments
  INTO TABLE @DATA(lt_docs).

SELECT
    " Artikelnummer auf 10 Stellen mit führenden Nullen
    LPAD( material_id, 10, '0' ) AS material_id_padded,

    " Beschreibung auf feste Breite
    RPAD( description, 40, ' ' ) AS description_fixed
  FROM zmaterials
  INTO TABLE @DATA(lt_mats).

SELECT
    email,
    " Position von @ finden
    INSTR( email, '@' ) AS at_position,
    " Domain extrahieren
    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
      " Zeitbasierte Klassifizierung
      WHEN DATS_DAYS_BETWEEN( order_date, $session.system_date ) <= 7
        THEN 'Diese Woche'
      WHEN DATS_DAYS_BETWEEN( order_date, $session.system_date ) <= 30
        THEN 'Dieser Monat'
      WHEN DATS_DAYS_BETWEEN( order_date, $session.system_date ) <= 90
        THEN 'Dieses Quartal'
      ELSE 'Älter'
    END AS age_category,

    CASE
      " Wertbasierte Klassifizierung mit Ranges
      WHEN amount < 100 THEN 'Klein'
      WHEN amount BETWEEN 100 AND 999 THEN 'Mittel'
      WHEN amount BETWEEN 1000 AND 9999 THEN 'Groß'
      ELSE 'Enterprise'
    END AS size_category
  FROM zorders
  INTO TABLE @DATA(lt_orders).

SELECT
    customer_id,
    " Erste nicht-leere Alternative
    COALESCE( phone_mobile, phone_office, phone_home, 'Keine Nummer' )
      AS contact_phone,

    " Leere Strings als NULL behandeln
    COALESCE( NULLIF( email, '' ), '[email protected]' ) AS email_safe,

    " Division by Zero verhindern
    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-Daten mit neuen SQL-Features
    SELECT
        region,
        product_category,
        CAST( order_date AS CHAR( 7 ) ) AS month,  " YYYY-MM

        " Basis-Aggregationen
        COUNT( * ) AS order_count,
        SUM( amount ) AS total_sales,
        AVG( amount ) AS avg_order_value,

        " Neue Aggregatfunktionen
        MEDIAN( amount ) AS median_order_value,
        STRING_AGG( DISTINCT salesperson, ', ' ORDER BY salesperson )
          AS active_salespeople,

        " Perzentile für Segmentierung
        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).

    " Ausgabe
    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.

    " Gesamtübersicht mit ROLLUP
    out->write( '' ).
    out->write( '=== Zusammenfassung (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( |GESAMT: { 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.

" ALT: Mehrere Abfragen
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).

" NEU: Eine Abfrage
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).