Start SAP ABAP SQL OpenSQL Archiv nach Kategorie "SQL expressions"

Kategorie: SQL expressions

[ABAP] Pivot-Darstellung von Feldwerten eines CDS-Views / SELECT-Statements (CASE, SUM, STRING_AGG, substring_regexpr, RegEx, PCRE, UNION, COLLECT)

Variante 1 (CDS-View mit CASE, SUM, GROUP BY)

* Daten werden hier mit CDS-View R_ProdValuationLedgerAccountTP (Product Valuation Ledger Account - TP) gelesen
* Suche der Periodischen Verrechnungspreise im Material Ledger über
* CDS-View r_prodvaluationledgeraccounttp (Product Valuation Ledger Account - TP).
* Das CDS-View liefert zeilenweise zu jedem Währungstyp einen Preis, die jeweils leeren Zeilen für die Preise
* (price_10, price_30, price_31) werden mit dem Wert '0.0' vorbelegt.
* Durch die Summenbildung pro Gruppe werden die Datensätze zu einer Zeile mit den zug. Preisen in Spalten zusammengefasst.
* Die Typkonvertierung abap.curr <-> abap.dec ist notwendig, da die Funktion sum( ) nur mit abap.dec arbeitet.
@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Testview für PIVOT'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
define view entity Z_CDS_PIVOT
  as select from R_ProdValuationLedgerAccountTP
{
  key Product,
  key ValuationArea,
  key ValuationType,
  @EndUserText.label: 'Ledger'
  key Ledger,
      Currency,
  // Summe der Spalte MovingAveragePrice für Währungstyp 10
  @EndUserText.label: 'Preis WT 10'
  @Semantics.amount.currencyCode: 'Currency'
      cast(sum(case when CurrencyRole = '10' then cast(MovingAveragePrice as abap.dec( 16, 2 )) else 0.0 end) as abap.curr( 16, 2 )) as price_10,
  // Summe der Spalte MovingAveragePrice für Währungstyp 30
  @EndUserText.label: 'Preis WT 30'
  @Semantics.amount.currencyCode: 'Currency'
      cast(sum(case when CurrencyRole = '30' then cast(MovingAveragePrice as abap.dec( 16, 2 )) else 0.0 end) as abap.curr( 16, 2 )) as price_30,
  // Summe der Spalte MovingAveragePrice für Währungstyp 31
  @EndUserText.label: 'Preis WT 31'
  @Semantics.amount.currencyCode: 'Currency'
      cast(sum(case when CurrencyRole = '31' then cast(MovingAveragePrice as abap.dec( 16, 2 )) else 0.0 end) as abap.curr( 16, 2 )) as price_31
}
// Gruppierung, damit die Summenbildung oben pro Gruppe (Produkt / Währungstyp) erfolgt
group by Product,
         ValuationArea,
         ValuationType,
         Ledger,
         Currency,
         MovingAveragePrice

Variante 2 (SELECT, SUM, CASE, GROUP BY)

* Im Beispiel werden durch Anweisungen in einem SELECT-Statement mehrere Zeilen einer Rückgabemenge zu einer Zeile zusammengefasst (gruppiert).
* Kern der Funktion zur Umwandlung der Zeilenwerte in Spaltenwerte sind die Funktionen:
*  SUM( )
*  CASE
*  GROUP BY

PARAMETERS: p_matnr TYPE r_prodvaluationledgeraccounttp-product DEFAULT '1122334455'.
PARAMETERS: p_bwkey TYPE r_prodvaluationledgeraccounttp-valuationarea DEFAULT '0001'.
PARAMETERS: p_bwtar TYPE r_prodvaluationledgeraccounttp-valuationtype DEFAULT ''.
PARAMETERS: p_ledger TYPE r_prodvaluationledgeraccounttp-ledger DEFAULT '0L'.

START-OF-SELECTION.
* Suche der Periodischen Verrechnungspreise im Material Ledger über
* CDS-View r_prodvaluationledgeraccounttp (Product Valuation Ledger Account - TP).
* Das SELECT liefert zeilenweise zu jedem Währungstyp einen Preis, die jeweils leeren Zeilen für die Preise
* (price_10, price_30, price_31) werden mit dem Wert dec`0.00` vorbelegt.
* Durch die Summenbildung pro Gruppe werden die Datensätze zu einer Zeile mit den zug. Preisen in Spalten zusammengefasst.
  SELECT FROM r_prodvaluationledgeraccounttp AS l
    FIELDS
      l~product,
      l~valuationarea,
      l~valuationtype,
      l~ledger,
      l~currency,
* Summe der Spalte MovingAveragePrice für Währungstyp 10
      SUM( CASE WHEN currencyrole = '10' THEN movingaverageprice ELSE dec`0.00` END ) AS price_10,
* Summe der Spalte MovingAveragePrice für Währungstyp 30
      SUM( CASE WHEN currencyrole = '30' THEN movingaverageprice ELSE dec`0.00` END ) AS price_30,
* Summe der Spalte MovingAveragePrice für Währungstyp 31
      SUM( CASE WHEN currencyrole = '31' THEN movingaverageprice ELSE dec`0.00` END ) AS price_31
    WHERE l~product       EQ @p_matnr
      AND l~valuationarea EQ @p_bwkey
      AND l~valuationtype EQ @p_bwtar
      AND l~ledger        EQ @p_ledger
* Gruppierung, damit die Summenbildung oben pro Gruppe (Produkt / Währungstyp) erfolgt
    GROUP BY l~product,
             l~valuationarea,
             l~valuationtype,
             l~ledger,
             l~currency,
             l~movingaverageprice
    ORDER BY l~product
    INTO TABLE @DATA(it_product).

  IF sy-subrc = 0.
* Daten im Inline-Browser im SAP-Fenster anzeigen
    cl_abap_browser=>show_html( EXPORTING title       = 'Pivot'
                                          html_string = cl_demo_output=>new( )->write_data( it_product )->get( )
                                          container   = cl_gui_container=>default_screen ).

* cl_gui_container=>default_screen erzwingen
    WRITE: space.
  ENDIF.

Variante 3 (SELECT, STRING_AGG, substring_regexpr, GROUP BY)

* Im Beispiel werden durch Anweisungen in einem SELECT-Statement mehrere Zeilen einer Rückgabemenge zu einer Zeile zusammengefasst (gruppiert).
* Kern der Funktion zur Umwandlung der Zeilenwerte in Spaltenwerte sind die Funktionen:
*  STRING_AGG( )
*  substring_regexpr( ) mit PCRE-RegEx
*  GROUP BY
 
* als Konstanten für die PCRE-RegEx sind nur elementare Datentypen (keine Strings) erlaubt (hier: CHAR)
* die Längen der Zeichenketten müssen exakt stimmen,
* sonst funktioniert das Pattern-Matching bei der RegEx nicht
* zum Testen der PCRE-RegEx siehe auch: https://regex101.com/
CONSTANTS: co_part1 TYPE char11 VALUE '^(\d+\.\d+)'.
CONSTANTS: co_part2 TYPE char21 VALUE '^\d+\.\d+\|\K\d+\.\d+'.
CONSTANTS: co_part3 TYPE char31 VALUE '^\d+\.\d+\|\d+\.\d+\|\K\d+\.\d+'.
 
PARAMETERS: p_matnr TYPE i_prodvalnledgeraccounttp_2-product DEFAULT '1122334455'.
PARAMETERS: p_bwkey TYPE i_prodvalnledgeraccounttp_2-valuationarea DEFAULT '0001'.
PARAMETERS: p_bwtar TYPE i_prodvalnledgeraccounttp_2-valuationtype DEFAULT ''.
PARAMETERS: p_ledger TYPE i_prodvalnledgeraccounttp_2-ledger DEFAULT '0L'.
 
START-OF-SELECTION.
* Suche der Periodischen Verrechnungspreise im Material Ledger über
* CDS-View I_PRODVALNLEDGERACCOUNTTP_2 (Product Valuation Ledger Account - TP)
* das SELECT liefert zeilenweise zu jedem Währungstyp einen Preis
* über STRING_AGG werden die Preise zu einer Gruppe mit Trennzeichen zusammengefasst
* mit substring_regexpr kann über eine RegEx dann ein Teil (erster, mittlerer oder letzter) dieser Gruppe extrahiert werden
  SELECT FROM i_prodvalnledgeraccounttp_2 AS pa
    FIELDS
      pa~product,
      pa~valuationarea,
      pa~ledger,
      pa~productpriceunitquantity,
      pa~currency,
      pa~standardprice,
      pa~companycode,
      pa~baseunit,
* Beispiel: periodische Verrechnungspreise als Gruppe zu einem String mit Trennzeichen "," zusammenfassen
      STRING_AGG( CAST( pa~movingaverageprice AS CHAR ), ', ' ) AS avg_price_all,
* Beispiel: periodische Verrechnungspreise als Gruppe zu einem String mit Trennzeichen "|" zusammenfassen
*           und über eine PCRE-RegEx den ersten Wert bis zum Trennzeichen "|" in eine Spalte avg_price1 schreiben
      substring_regexpr( pcre = @co_part1, value = STRING_AGG( CAST( pa~movingaverageprice AS CHAR ), '|' ) ) AS avg_price1,
* Beispiel: periodische Verrechnungspreise als Gruppe zu einem String mit Trennzeichen "|" zusammenfassen
*           und über eine PCRE-RegEx den mittleren Wert von "|" bis "|" in eine Spalte avg_price2 schreiben
      substring_regexpr( pcre = @co_part2, value = STRING_AGG( CAST( pa~movingaverageprice AS CHAR ), '|' ) ) AS avg_price2,
* Beispiel: periodische Verrechnungspreise als Gruppe zu einem String mit Trennzeichen "|" zusammenfassen
*           und über eine PCRE-RegEx den letzen Wert ab "|" in eine Spalte avg_price3 schreiben
      substring_regexpr( pcre = @co_part3, value = STRING_AGG( CAST( pa~movingaverageprice AS CHAR ), '|' ) ) AS avg_price3
    WHERE pa~product       EQ @p_matnr
      AND pa~valuationarea EQ @p_bwkey
      AND pa~valuationtype EQ @p_bwtar
      AND pa~ledger        EQ @p_ledger
    GROUP BY pa~product,
             pa~valuationarea,
             pa~ledger,
             pa~productpriceunitquantity,
             pa~currency,
             pa~standardprice,
             pa~companycode,
             pa~baseunit
    ORDER BY pa~product
    INTO TABLE @DATA(it_product).
 
  IF sy-subrc = 0.
    TRY.
* SALV-Table
        DATA: o_salv TYPE REF TO cl_salv_table.
 
        cl_salv_table=>factory( IMPORTING r_salv_table = o_salv
                                CHANGING  t_table      = it_product ).
 
* Grundeinstellungen
        o_salv->get_functions( )->set_all( abap_true ).
        o_salv->get_columns( )->set_optimize( abap_true ).
        o_salv->get_display_settings( )->set_list_header( 'Pivot' ).
        o_salv->get_display_settings( )->set_striped_pattern( abap_true ).
        o_salv->get_selections( )->set_selection_mode( if_salv_c_selection_mode=>row_column ).
 
* Spaltenüberschriften: technischer Name und Beschreibungstexte, Short Text und Medium Text leer lassen für Autosize
        LOOP AT o_salv->get_columns( )->get( ) ASSIGNING FIELD-SYMBOL(<c>).
          DATA(o_col) = <c>-r_column.
          o_col->set_short_text( || ).
          o_col->set_medium_text( || ).
          IF o_col->get_long_text( ) IS INITIAL.
            o_col->set_long_text( |{ o_col->get_columnname( ) }| ).
          ELSE.
            o_col->set_long_text( |{ o_col->get_long_text( ) }| ).
          ENDIF.
        ENDLOOP.
 
        o_salv->display( ).
      CATCH cx_root INTO DATA(e_txt).
        WRITE: / e_txt->get_text( ).
    ENDTRY.
  ENDIF.

Variante 4 (SELECT, UNION, COLLECT)

TYPES: BEGIN OF ty_s_product,
         product TYPE i_prodvalnledgeraccounttp_2-product,
* Platzhalter für die Periodischen Verrechnungspreise je Währungstyp
         p1      TYPE i_prodvalnledgeraccounttp_2-movingaverageprice,
         p2      TYPE i_prodvalnledgeraccounttp_2-movingaverageprice,
         p3      TYPE i_prodvalnledgeraccounttp_2-movingaverageprice,
       END OF ty_s_product.

TYPES: ty_it_product TYPE STANDARD TABLE OF ty_s_product WITH DEFAULT KEY.

PARAMETERS: p_matnr TYPE i_prodvalnledgeraccounttp_2-product DEFAULT '1122334455'.
PARAMETERS: p_bwkey TYPE i_prodvalnledgeraccounttp_2-valuationarea DEFAULT '0001'.
PARAMETERS: p_bwtar TYPE i_prodvalnledgeraccounttp_2-valuationtype DEFAULT ''.
PARAMETERS: p_ledger TYPE i_prodvalnledgeraccounttp_2-ledger DEFAULT '0L'.

START-OF-SELECTION.

  DATA: it_product_union TYPE ty_it_product.

* Suche der Periodischen Verrechnungspreise pro Währungstyp im Material Ledger über
* CDS-View I_PRODVALNLEDGERACCOUNTTP_2 (Product Valuation Ledger Account - TP)
* Die Datensätze pro Währungstyp werden hier über ein UNION-Statement zu einer Ausgabemenge
* zusammengeführt. Dabei werden die jeweils leeren Preise für p1, p2, p3 mit dec`0.00` vorbelegt,
* damit später bei COLLECT die Werte korrekt in einer Zeile verdichtet werden.

* erstes SELECT für Währungstyp '10'
  SELECT FROM i_prodvalnledgeraccounttp_2 AS pa
    FIELDS
        pa~product,
        pa~movingaverageprice AS p1,
        dec`0.00` AS p2,
        dec`0.00` AS p3
    WHERE pa~product       EQ @p_matnr
      AND pa~valuationarea EQ @p_bwkey
      AND pa~valuationtype EQ @p_bwtar
      AND pa~ledger        EQ @p_ledger
      AND pa~currencyrole  EQ '10'
  UNION
* zweites SELECT für Währungstyp '30'
  SELECT FROM i_prodvalnledgeraccounttp_2 AS pa
    FIELDS
        pa~product,
        dec`0.00` AS p1,
        pa~movingaverageprice AS p2,
        dec`0.00` AS p3
    WHERE pa~product       EQ @p_matnr
      AND pa~valuationarea EQ @p_bwkey
      AND pa~valuationtype EQ @p_bwtar
      AND pa~ledger        EQ @p_ledger
      AND pa~currencyrole  EQ '30'
  UNION
* drittes SELECT für Währungstyp '31'
  SELECT FROM i_prodvalnledgeraccounttp_2 AS pa
    FIELDS
        pa~product,
        dec`0.00` AS p1,
        dec`0.00` AS p2,
        pa~movingaverageprice AS p3
    WHERE pa~product       EQ @p_matnr
      AND pa~valuationarea EQ @p_bwkey
      AND pa~valuationtype EQ @p_bwtar
      AND pa~ledger        EQ @p_ledger
      AND pa~currencyrole  EQ '31'
  INTO CORRESPONDING FIELDS OF TABLE @it_product_union.

  IF sy-subrc = 0.
* Daten-Aufbereitung
    DATA: it_product_pivot TYPE HASHED TABLE OF ty_s_product WITH UNIQUE KEY product.

    LOOP AT it_product_union ASSIGNING FIELD-SYMBOL(<p>).
* COLLECT verdichtet gleiche Datensätze zu einem Datensatz, verwendet intern HASHED TABLES
* Zu beachten ist dabei, dass beim Verdichten die Zahlenwerte (int, fltp, dec usw.)
* der einzelnen Zeilen summiert werden!
      COLLECT <p> INTO it_product_pivot.
    ENDLOOP.

* Daten im Inline-Browser im SAP-Fenster anzeigen
    cl_abap_browser=>show_html( EXPORTING title       = 'Pivot'
                                          html_string = cl_demo_output=>new( )->write_data( it_product_union )->write_data( it_product_pivot )->get( )
                                          container   = cl_gui_container=>default_screen ).

* cl_gui_container=>default_screen erzwingen
    WRITE: space.

  ENDIF.

Links

[ABAP] Ergebnisse eines SQL-Ausdrucks in einer Zeile verketten (STRING_AGG)

STRING_AGG verkettet die Ergebnisse eines SQL-Ausdrucks in einer Zeile (Typ SSTRING, Länge 1333). Wenn der String länger als 1333 Zeichen ist, wird eine Ausnahme (CX_SY_OPEN_SQL_DB) geworfen. Die Beschränkung auf 1333 Zeichen kann durch die Funktion TO_CLOB umgangen werden.

Variante 1 (transparente Tabelle)

SELECT FROM t100
  FIELDS arbgb,
         to_clob( STRING_AGG( text, ', ' ) ) AS text_agg
WHERE sprsl = 'D'
GROUP BY arbgb
ORDER BY arbgb
INTO TABLE @DATA(it_t100)
UP TO 10 ROWS.

IF sy-subrc = 0.
  cl_demo_output=>write_data( it_t100 ).

* HTML-Code vom Demo-Output holen
  DATA(lv_html) = cl_demo_output=>get( ).

* Daten im Inline-Browser im SAP-Fenster anzeigen
  cl_abap_browser=>show_html( EXPORTING title       = 'T100'
                                        html_string = lv_html
                                        container   = cl_gui_container=>default_screen ).

* cl_gui_container=>default_screen erzwingen
  WRITE: space.
ENDIF.

Variante 2 (interne Tabelle)

* interne Tabelle anlegen
TYPES: BEGIN OF ty_s_values,
         name TYPE char5,
         text TYPE char3,
       END OF ty_s_values.

TYPES: ty_it_values TYPE STANDARD TABLE OF ty_s_values WITH DEFAULT KEY.

DATA(it_values) = VALUE ty_it_values(
                                      ( name = 'TEXT1' text = '123' )
                                      ( name = 'TEXT1' text = '789' )
                                      ( name = 'TEXT1' text = '456' )
                                      ( name = 'TEXT2' text = 'abc' )
                                      ( name = 'TEXT2' text = 'def' )
                                      ( name = 'TEXT2' text = 'ghi' )
                                      ( name = 'TEXT3' text = '***' )
                                      ( name = 'TEXT3' text = '---' )
                                      ( name = 'TEXT3' text = '+++' )
                                    ).

* Daten aus der internen Tabelle selektieren
* Daten in der Spalte 'text' sortieren und verketten
SELECT FROM @it_values AS val
  FIELDS val~name,
         to_clob( STRING_AGG( val~text, ', ' ORDER BY text ) ) AS text_agg
WHERE val~name = 'TEXT1'
GROUP BY val~name
ORDER BY val~name
INTO TABLE @DATA(it_agg).

IF sy-subrc = 0.
  cl_demo_output=>write_data( it_agg ).

* HTML-Code vom Demo-Output holen
  DATA(lv_html) = cl_demo_output=>get( ).

* Daten im Inline-Browser im SAP-Fenster anzeigen
  cl_abap_browser=>show_html( EXPORTING title       = 'TXT'
                                        html_string = lv_html
                                        container   = cl_gui_container=>default_screen ).

* cl_gui_container=>default_screen erzwingen
  WRITE: space.
ENDIF.

[ABAP] OpenSQL: Aggregatfunktionen 

* DEMO_SQL_EXPR_IN_AGGREGATES

* Funktionen:
*   AVG
*   MAX
*   MIN
*   MEDIAN
*   SUM
*   CORR
*   COUNT
*   GROUPING

Links

[ABAP] OpenSQL: Coalesce – Fehlende SQL-Werte (NULL) ersetzen 

* DEMO_SQL_EXPR_COALESCE

* Null-Werte (Nicht vorhandene Email-Adressen), die sich aus dem LEFT OUTER JOIN ergeben, werden durch den String '<leer>' ersetzt
CONSTANTS: co_no_email TYPE ad_smtpadr VALUE '<leer>'.

SELECT u~bname,
       u~persnumber,
       u~addrnumber,
       COALESCE( a~smtp_addr, @co_no_email ) AS email
  INTO TABLE @DATA(it_usr)
  FROM usr21 AS u
  LEFT OUTER JOIN adr6 AS a ON a~persnumber = u~persnumber AND a~addrnumber = u~addrnumber.

IF sy-subrc = 0.
  cl_demo_output=>display( it_usr ).
ENDIF.

Links

[ABAP] OpenSQL: Abhängigkeit (CASE)

Variante 1

SELECT matnr,
       CASE meins
          WHEN 'ST' THEN 'Stück'
          WHEN 'KG' THEN 'Kg'
          ELSE 'andere Einheit'
       END AS meins_desc,
       maktx
  INTO TABLE @DATA(it_mara)
  FROM marav.

cl_demo_output=>display( it_mara ).

Variante 2

SELECT matnr,
       CASE
          WHEN meins = 'ST' THEN 'Stück'
          WHEN meins = 'KG' THEN 'Kg'
          ELSE 'andere Einheit'
       END AS meins_desc,
       maktx
  INTO TABLE @DATA(it_mara)
  FROM marav.

cl_demo_output=>display( it_mara ).

Links

[ABAP] OpenSQL: Summenbildung (SUM) mit Abhängigkeit (CASE)

Variante 1

* Summenbildung, abhängig von Spalte Soll/Haben
DATA: lv_bsid_sum type bsid-dmbtr.

SELECT SUM( CASE shkzg
* Haben
                WHEN 'H' THEN dmbtr
* Soll, negieren für Subtraktion
                WHEN 'S' THEN dmbtr * -1
            END )
  INTO @lv_bsid_sum
  FROM bsid
  WHERE bukrs = '0040'.

IF sy-subrc = 0.
* Summierten Wert ausgeben
  WRITE: / lv_bsid_sum.
ENDIF.

Variante 2

* Summenbildung, abhängig von Spalte Soll/Haben
DATA: lv_bsid_sum type bsid-dmbtr.

SELECT SUM( CASE
* Haben
                WHEN shkzg = 'H' THEN dmbtr
* Soll, negieren für Subtraktion
                WHEN shkzg = 'S' THEN dmbtr * -1
            END )
  INTO @lv_bsid_sum
  FROM bsid
  WHERE bukrs = '0040'.

IF sy-subrc = 0.
* Summierten Wert ausgeben
  WRITE: / lv_bsid_sum.
ENDIF.