Nouveautés ABAP SQL 2024/2025 : Les fonctionnalités les plus importantes en un coup d

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Hiérarchie organisationnelle"
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.
    " Fonctions de hiérarchie dans 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,
        " Fonctions spécifiques à la hiérarchie
        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( '=== Hiérarchie organisationnelle ===' ).
    LOOP AT lt_hierarchy INTO DATA(ls_org).
      " Indentation basée sur le niveau
      DATA(lv_indent) = repeat( val = '  ' occ = ls_org-level ).
      out->write( |{ lv_indent }{ ls_org-orgname } (Niveau { ls_org-level })| ).
    ENDLOOP.

    " Interroger uniquement certains niveaux
    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  " Uniquement les 2 premiers niveaux
      INTO TABLE @DATA(lt_top_levels).

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

ENDCLASS.

" Nombre d'employés par unité organisationnelle incluant les sous-unités
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,      " Employés directs
    total_employees      " Incluant toutes les sous-unités
  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.
    " Salaire médian par département
    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( '=== Statistiques salariales par département ===' ).
    LOOP AT lt_stats INTO DATA(ls_stat).
      out->write( |Département { ls_stat-department_id }:| ).
      out->write( |  Moyenne: { ls_stat-avg_salary DECIMALS = 2 }| ).
      out->write( |  Médiane:       { ls_stat-median_salary DECIMALS = 2 }| ).
      out->write( |  Plage:       { ls_stat-min_salary } - { ls_stat-max_salary }| ).
      out->write( '' ).
    ENDLOOP.

    " Comparaison : Médiane vs Moyenne montre la distribution
    " Médiane < Moyenne = beaucoup de valeurs basses, quelques valeurs élevées
    " Médiane > Moyenne = beaucoup de valeurs élevées, quelques valeurs basses
  ENDMETHOD.

ENDCLASS.

" Calculer les percentiles de salaire
SELECT department_id,
       " Percentile continu (interpolé)
       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,
       " Percentile discret (valeur réelle la plus proche)
       PERCENTILE_DISC( 0.50 ) WITHIN GROUP ( ORDER BY salary )
         AS median_discrete
  FROM zemployees
  GROUP BY department_id
  INTO TABLE @DATA(lt_percentiles).

" Application : Définir des classes de salaire
" < 25%: Débutant
" 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.
    " Tous les noms d'employés par département en tant que chaîne
    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( |Département { ls_dept-department_id } ({ ls_dept-count } employés):| ).
      out->write( |  { ls_dept-all_employees }| ).
      out->write( '' ).
    ENDLOOP.

    " Fusionner les tags d'un article
    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 avec 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.
    " Chiffre d'affaires selon différentes dimensions simultanément
    SELECT
        region,
        product_category,
        SUM( amount ) AS total_sales,
        COUNT( * ) AS order_count,
        " GROUPING() indique si la colonne a été agrégée (1) ou non (0)
        GROUPING( region ) AS region_grouped,
        GROUPING( product_category ) AS category_grouped
      FROM zsales_orders
      GROUP BY GROUPING SETS (
        ( region, product_category ),  " Par région et catégorie
        ( region ),                     " Uniquement par région
        ( product_category ),           " Uniquement par catégorie
        ( )                             " Total général
      )
      ORDER BY region, product_category
      INTO TABLE @DATA(lt_sales).

    out->write( '=== Analyse des ventes (GROUPING SETS) ===' ).
    LOOP AT lt_sales INTO DATA(ls_sale).
      CASE ls_sale-region_grouped.
        WHEN 1.
          " Région agrégée
          CASE ls_sale-category_grouped.
            WHEN 1.
              " Les deux agrégés = Total général
              out->write( |TOTAL: { ls_sale-total_sales }| ).
            WHEN 0.
              " Uniquement par catégorie
              out->write( |  Catégorie { ls_sale-product_category }: { ls_sale-total_sales }| ).
          ENDCASE.
        WHEN 0.
          " Région non agrégée
          CASE ls_sale-category_grouped.
            WHEN 1.
              " Uniquement par région
              out->write( |Région { ls_sale-region }: { ls_sale-total_sales }| ).
            WHEN 0.
              " Par région et catégorie
              out->write( |  { ls_sale-region } - { ls_sale-product_category }: { ls_sale-total_sales }| ).
          ENDCASE.
      ENDCASE.
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

" Chiffre d'affaires avec sous-totaux : Région -> Catégorie -> Produit
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).

" Résultat :
" NORD | Électronique | Laptop   | 5000   (Produit)
" NORD | Électronique | Moniteur  | 2000   (Produit)
" NORD | Électronique | NULL     | 7000   (Somme catégorie)
" NORD | Logiciel   | Office   | 1500   (Produit)
" NORD | Logiciel   | NULL     | 1500   (Somme catégorie)
" NORD | NULL       | NULL     | 8500   (Somme région)
" NULL | NULL       | NULL     | 15000  (Somme totale)

" Chiffre d'affaires pour toutes les combinaisons de région et catégorie
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).

" Le résultat contient :
" - Par région et catégorie
" - Uniquement par région (toutes catégories)
" - Uniquement par catégorie (toutes régions)
" - Somme totale

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Commandes avec filtre"
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,

  " Champ calculé avec héritage d'annotation
  @Aggregation.default: #SUM
  _Items.TotalQuantity as TotalQuantity
}

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

      " Redirection vers une autre vue pour les articles
      @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
    " Supprimer les zéros de tête
    LTRIM( document_number, '0' ) AS clean_doc_num,

    " Supprimer les espaces et caractères spéciaux de fin
    RTRIM( description, ' .-_' ) AS clean_description,

    " Combiné : Supprimer les caractères de début/fin
    RTRIM( LTRIM( code, '0' ), '0' ) AS clean_code
  FROM zdocuments
  INTO TABLE @DATA(lt_docs).

SELECT
    " Numéro d'article sur 10 positions avec zéros de tête
    LPAD( material_id, 10, '0' ) AS material_id_padded,

    " Description à largeur fixe
    RPAD( description, 40, ' ' ) AS description_fixed
  FROM zmaterials
  INTO TABLE @DATA(lt_mats).

SELECT
    email,
    " Trouver la position de @
    INSTR( email, '@' ) AS at_position,
    " Extraire le domaine
    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
      " Classification basée sur le temps
      WHEN DATS_DAYS_BETWEEN( order_date, $session.system_date ) <= 7
        THEN 'Cette semaine"
      WHEN DATS_DAYS_BETWEEN( order_date, $session.system_date ) <= 30
        THEN 'Ce mois"
      WHEN DATS_DAYS_BETWEEN( order_date, $session.system_date ) <= 90
        THEN 'Ce trimestre"
      ELSE 'Plus ancien"
    END AS age_category,

    CASE
      " Classification basée sur la valeur avec plages
      WHEN amount < 100 THEN 'Petit"
      WHEN amount BETWEEN 100 AND 999 THEN 'Moyen"
      WHEN amount BETWEEN 1000 AND 9999 THEN 'Grand"
      ELSE 'Entreprise"
    END AS size_category
  FROM zorders
  INTO TABLE @DATA(lt_orders).

SELECT
    customer_id,
    " Première alternative non vide
    COALESCE( phone_mobile, phone_office, phone_home, 'Aucun numéro' )
      AS contact_phone,

    " Traiter les chaînes vides comme NULL
    COALESCE( NULLIF( email, '' ), '[email protected]' ) AS email_safe,

    " Éviter la division par zéro
    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.
    " Données du tableau de bord avec les nouvelles fonctionnalités SQL
    SELECT
        region,
        product_category,
        CAST( order_date AS CHAR( 7 ) ) AS month,  " YYYY-MM

        " Agrégations de base
        COUNT( * ) AS order_count,
        SUM( amount ) AS total_sales,
        AVG( amount ) AS avg_order_value,

        " Nouvelles fonctions d'agrégation
        MEDIAN( amount ) AS median_order_value,
        STRING_AGG( DISTINCT salesperson, ', ' ORDER BY salesperson )
          AS active_salespeople,

        " Percentiles pour la 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).

    " Sortie
    out->write( '=== Tableau de bord des ventes 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( |=== Région: { ls_row-region } ===| ).
        lv_current_region = ls_row-region.
      ENDIF.

      out->write( |{ ls_row-product_category } ({ ls_row-month }):| ).
      out->write( |  Commandes: { ls_row-order_count }, | &&
                  |Total: { ls_row-total_sales DECIMALS = 2 }| ).
      out->write( |  Moy: { ls_row-avg_order_value DECIMALS = 2 }, | &&
                  |Médiane: { 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( |  Vendeurs: { ls_row-active_salespeople }| ).
    ENDLOOP.

    " Vue d'ensemble avec ROLLUP
    out->write( '' ).
    out->write( '=== Résumé (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 } commandes)| ).
        WHEN xsdbool( ls_sum-product_category IS INITIAL ).
          out->write( |  Région { 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.

" ANCIEN : Plusieurs requêtes
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).

" NOUVEAU : Une requête
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).