Startseite Beiträge getaggt mit "OpenSQL" (Seite 3)

Schlagwort: OpenSQL

[OpenSQL] Fünf goldene SQL-Regeln

  1. Ergebnismenge reduzieren (kein SELECT…ENDSELECT, kein LOOP … SELECT … ENDLOOP, WHERE-clause genau spezifizieren)
  2. Transferdatenmenge minimieren (Aggregatfunktionen [MIN, MAX, AVG, SUM, COUNT …] verwenden, UPDATE mit SET, kein SELECT *)
  3. Anzahl Datentransfers minimieren (JOINs und SUBSELECTs verwenden, SELECT … FOR ALL ENTRIES verwenden, INSERT, UPDATE, MODIFY, DELETE immer mit Arrays)
  4. Suchaufwand reduzieren (Secondary Keys & Indexes verwenden, positive WHERE-Bedingungen verwenden)
  5. Reduktion des DB-Load (keine redundanten Zugriffe, Buffering/Caching verwenden, SORT auf dem Applikationsserver)

[ABAP] Case-insensitives SORT von internen Tabellen 

TYPES: BEGIN OF ty_name,
         name    TYPE string,
         name_lc TYPE string,
       END OF ty_name.

TYPES: ty_it_name TYPE STANDARD TABLE OF ty_name WITH DEFAULT KEY.

* Tabelle mit Strings, die sortiert werden soll
DATA(it_names) = VALUE ty_it_name( ( name = 'Albert' )
                                   ( name = 'Emma' )
                                   ( name = 'Zorro' )
                                   ( name = 'Donald' )
                                   ( name = 'Lion' )
                                   ( name = 'answer' )
                                   ( name = 'type' )
                                   ( name = 'second' ) ).

* zweite Spalte mit lower-case Strings füllen
LOOP AT it_names ASSIGNING FIELD-SYMBOL(<n>).
  <n>-name_lc = to_lower( <n>-name ).
ENDLOOP.

* erstmal regulär sortieren
SORT: it_names BY name.

* zweite Tabelle anlegen (kopieren)
DATA(it_names_lc) = it_names.

* Spalte lower-case sortieren
SORT: it_names_lc BY name_lc.

* Ausgabe der Ergebnisse
cl_demo_output=>write_data( value = it_names    name = 'case-sensitives SORT' ).
cl_demo_output=>write_data( value = it_names_lc name = 'SORT nach lower-case' ).
cl_demo_output=>display( ).

[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.

[ABAP] OpenSQL: Rückgabedaten eines SELECTs in einzelne Variablen übergeben 

DATA: lv_name_first TYPE ad_namefir.
DATA: lv_name_last TYPE ad_namelas.
DATA: lv_smtp_addr TYPE ad_smtpadr.

SELECT SINGLE p~name_first, p~name_last, a~smtp_addr
  FROM usr21 AS u
  INNER JOIN adrp AS p ON p~persnumber = u~persnumber
  INNER JOIN adr6 AS a ON a~addrnumber = u~addrnumber AND a~persnumber = u~persnumber
  INTO (@lv_name_first, @lv_name_last, @lv_smtp_addr)
  WHERE u~bname = @sy-uname.

IF sy-subrc = 0.
  ...
ENDIF.

[ABAP] OpenSQL: WHERE-Bedingung mit Hilfe einer internen Tabelle (RANGE, STANDARD TABLE)

Variante 1 (RANGE)

* Range für MATNR definieren
TYPES: ty_rg_matnr TYPE RANGE OF matnr.

* MATNR für Suche
DATA(lv_matnr) = |12345|.

* RANGE für die Suche definieren
DATA(it_matnr) = VALUE ty_rg_matnr( ( sign   = 'I'
                                      option = 'EQ'
                                      low    = |{ lv_matnr WIDTH = 18 ALPHA = IN }|
                                      high   = '' ) ).

DATA: it_mara TYPE STANDARD TABLE OF mara WITH DEFAULT KEY.

SELECT * FROM mara INTO TABLE @it_mara WHERE matnr IN @it_matnr.

IF sy-subrc = 0.
  LOOP AT it_mara ASSIGNING FIELD-SYMBOL(<mat>).
    WRITE: / <mat>-matnr.
  ENDLOOP.
ELSE.
  WRITE: / 'Keine Daten vorhanden.'.
ENDIF.

Variante 2 (STANDARD TABLE)

* Zeilentyp für RANGE (STANDARD TABLE) für MATNR
TYPES: BEGIN OF ty_range,
         sign   TYPE ddsign,
         option TYPE ddoption,
         low    TYPE char18, " char18 für 18-stellige MATNR
         high   TYPE char18, " char18 für 18-stellige MATNR
       END OF ty_range.

* Tabellentyp für RANGE (STANDARD TABLE) für MATNR
TYPES: ty_rg_matnr TYPE STANDARD TABLE OF ty_range WITH DEFAULT KEY.

* MATNR für Suche
DATA(lv_matnr) = |12345|.

* RANGE für die Suche definieren
DATA(it_matnr) = VALUE ty_rg_matnr( ( sign   = 'I'
                                      option = 'EQ'
                                      low    = |{ lv_matnr WIDTH = 18 ALPHA = IN }|
                                      high   = '' ) ).

DATA: it_mara TYPE STANDARD TABLE OF mara WITH DEFAULT KEY.

SELECT * FROM mara INTO TABLE @it_mara WHERE matnr IN @it_matnr.

IF sy-subrc = 0.
  LOOP AT it_mara ASSIGNING FIELD-SYMBOL(<mat>).
    WRITE: / <mat>-matnr.
  ENDLOOP.
ELSE.
  WRITE: / 'Keine Daten vorhanden.'.
ENDIF.

[ABAP] OpenSQL: Dynamische WHERE-Condition

Variante 1 (Werteliste / Filter)

* Liste mit Benutzernamen
DATA(lv_users) = |'USER1', 'USER2'|.
* WHERE-Condition zusammenbauen
DATA(lv_where_condition) = |bname IN ({ lv_users })|.

TRY.
    DATA: it_usr TYPE STANDARD TABLE OF usr02 WITH DEFAULT KEY.

    SELECT * FROM usr02 INTO TABLE @it_usr WHERE (lv_where_condition).

    IF sy-subrc = 0.
      SORT: it_usr BY trdat DESCENDING bname ASCENDING.

      WRITE: / |USER         \| DATE       \| TIME     \| CREATED|.
      WRITE: / |-------------------------------------------------|.

      LOOP AT it_usr ASSIGNING FIELD-SYMBOL(<usr>).
        WRITE: / <usr>-bname, '|', <usr>-trdat, '|', <usr>-ltime, '|', <usr>-erdat.
      ENDLOOP.
    ENDIF.
  CATCH cx_root INTO DATA(e_txt).
    WRITE: / e_txt->get_text( ).
ENDTRY.

Variante 2 (Mehrzeilige WHERE-Bedingung)

* Liste mit Benutzernamen
DATA(lv_users) = |'USER1', 'USER2'|.
* Erstelldatum
DATA(lv_erdat) = |20100101|.

* WHERE-Condition zusammenbauen
DATA(it_where_condition) = VALUE stringtab( ( |bname IN ({ lv_users })| )
                                            ( |AND erdat > '{ lv_erdat }'| ) ).

TRY.
    DATA: it_usr TYPE STANDARD TABLE OF usr02 WITH DEFAULT KEY.

    SELECT * FROM usr02 INTO TABLE @it_usr WHERE (it_where_condition).

    IF sy-subrc = 0.
      SORT: it_usr BY trdat DESCENDING bname ASCENDING.

      WRITE: / |USER         \| DATE       \| TIME     \| CREATED|.
      WRITE: / |-------------------------------------------------|.

      LOOP AT it_usr ASSIGNING FIELD-SYMBOL(<usr>).
        WRITE: / <usr>-bname, '|', <usr>-trdat, '|', <usr>-ltime, '|', <usr>-erdat.
      ENDLOOP.
    ENDIF.
  CATCH cx_root INTO DATA(e_txt).
    WRITE: / e_txt->get_text( ).
ENDTRY.

[ABAP] OpenSQL: WHERE mit Werteliste 

DATA: it_usr TYPE STANDARD TABLE OF usr02 WITH DEFAULT KEY.

SELECT * FROM usr02 INTO TABLE it_usr WHERE bname IN ('XYZ', 'ZYX').

IF sy-subrc = 0.
  SORT: it_usr BY trdat DESCENDING bname ASCENDING.

  WRITE: / |USER         \| DATE       \| TIME     \| CREATED|.
  WRITE: / |-------------------------------------------------|.

  LOOP AT it_usr ASSIGNING FIELD-SYMBOL(<usr>).
    WRITE: / <usr>-bname, '|', <usr>-trdat, '|', <usr>-ltime, '|', <usr>-erdat.
  ENDLOOP.
ENDIF.

[ABAP] OpenSQL: Felder im SELECT mit konstanten Werten füllen 

* Variante 1 (Konstante)
CONSTANTS: co_empty TYPE char7 VALUE '<empty>'.
* Konstante über Alias maktx in das korrespondierende Feld MAKTX einfügen
SELECT matnr, @co_empty AS maktx FROM mara INTO TABLE @DATA(it_mara) UP TO 10 ROWS.

cl_demo_output=>display( it_mara ).

* Variante 2 (Variable)
DATA: lv_empty TYPE char7 VALUE '<empty>'.
* Konstante über Alias maktx in das korrespondierende Feld MAKTX einfügen
SELECT matnr, @lv_empty AS maktx FROM mara INTO TABLE @DATA(it_mara) UP TO 10 ROWS.

cl_demo_output=>display( it_mara ).