Startseite Beiträge getaggt mit "WHERE"

Schlagwort: WHERE

[ABAP] Dynamische WHERE-Clause für AMDP aus SELECT-OPTIONS / RANGES generieren

Bei Datenbankzugriffen mit AMDP / NATIVE SQL besteht nicht die Möglichkeit einfach RANGES in den SELECT-Statements zu übergeben.
Die RANGES müssen dazu zuerst in einen String umgewandelt werden. Dieser String kann dann in der WHERE-Condition verarbeitet werden.

Variante 1 (cl_shdb_seltab)

DATA: lv_usnam TYPE rkpf-usnam.

SELECT-OPTIONS: so_usnam FOR lv_usnam.

INITIALIZATION.

  so_usnam[] = VALUE #( ( sign = 'I' option = 'EQ' low = 'USER1' high = '' )
                        ( sign = 'I' option = 'EQ' low = 'USER2' high = '' )
                      ).

START-OF-SELECTION.
* wandelt ein SELECT-OPTIONS / RANGE in WHERE-String um
  TRY.
* WHERE-Condition ohne fehlerhaft gesetzte Klammern und unnötige Leerzeichen
      DATA(lv_where) = condense( cl_shdb_seltab=>new( so_usnam[] )->sql_where_condition( 'USNAM' ) ).

      WRITE: / lv_where.

    CATCH cx_root INTO DATA(e_txt).
  ENDTRY.

Variante 2 (cl_shdb_seltab, erzeugt fehlerhaftes WHERE-Statement)

DATA: lv_usnam TYPE rkpf-usnam.

SELECT-OPTIONS: so_usnam FOR lv_usnam.

INITIALIZATION.

  so_usnam[] = VALUE #( ( sign = 'I' option = 'EQ' low = 'USER1' high = '' )
                        ( sign = 'I' option = 'EQ' low = 'USER2' high = '' )
                      ).

START-OF-SELECTION.
* wandelt ein oder mehrere SELECT-OPTIONS / RANGE in WHERE-String um
  TRY.
* WHERE-Condition mit fehlerhaft gesetzte Klammern und unnötigen Leerzeichen
      DATA(lv_where) = cl_shdb_seltab=>combine_seltabs(
                                                        it_named_seltabs = VALUE #(
                                                                                    (
                                                                                      name = 'USNAM'              " RKPF-USNAM
                                                                                      dref = REF #( so_usnam[] )  " Referenz auf SELECT-OPTIONS / RANGE
                                                                                    )
                                                                                  )
                                                      ).

      WRITE: / lv_where.

    CATCH cx_root INTO DATA(e_txt).
  ENDTRY.

Variante 3 (cl_lib_seltab, OBSOLET)

DATA: lv_usnam TYPE rkpf-usnam.

SELECT-OPTIONS: so_usnam FOR lv_usnam.

INITIALIZATION.

  so_usnam[] = VALUE #( ( sign = 'I' option = 'EQ' low = 'USER1' high = '' )
                        ( sign = 'I' option = 'EQ' low = 'USER2' high = '' )
                      ).

START-OF-SELECTION.
* wandelt ein SELECT-OPTIONS / RANGE in WHERE-String um
  TRY.
* WHERE-Condition ohne fehlerhaft gesetzte Klammern und unnötige Leerzeichen
      DATA(lv_where) = condense( cl_lib_seltab=>new( so_usnam[] )->sql_where_condition( 'USNAM' ) ).

      WRITE: / lv_where.

    CATCH cx_root INTO DATA(e_txt).
  ENDTRY.

Variante 4 (FREE_SELECTIONS_RANGE_2_WHERE)

DATA: lv_usnam TYPE rkpf-usnam.

SELECT-OPTIONS: so_usnam FOR lv_usnam.

INITIALIZATION.

  so_usnam[] = VALUE #( ( sign = 'I' option = 'EQ' low = 'USER1' high = '' )
                        ( sign = 'I' option = 'EQ' low = 'USER2' high = '' )
                      ).

START-OF-SELECTION.

  DATA(it_ranges) = VALUE rsds_trange(
                                       (
* Tabellenname
                                         tablename = 'RKPF'
                                         frange_t = VALUE #(
                                                             (
* Feldname
                                                                fieldname = 'USNAM'
* RANGE aus SELECT-OPTIONS
                                                                selopt_t  = VALUE #( FOR <so> IN so_usnam
                                                                                     (
                                                                                       sign   = <so>-sign
                                                                                       option = <so>-option
                                                                                       low    = <so>-low
                                                                                       high   = <so>-high
                                                                                     )
                                                                                   )
                                                             )
                                                           )
                                       )
                                     ).

* Rückgabe
  DATA: it_where TYPE rsds_twhere.

* Freie Abgrenzungen: Konvertierung Format RSDS_TRANGE ==> RSDS_TWHERE
  CALL FUNCTION 'FREE_SELECTIONS_RANGE_2_WHERE'
    EXPORTING
      field_ranges  = it_ranges
    IMPORTING
      where_clauses = it_where.

* Ausgabe
  LOOP AT it_where ASSIGNING FIELD-SYMBOL(<e>).
    WRITE: / 'Table:', <e>-tablename.
    LOOP AT <e>-where_tab ASSIGNING FIELD-SYMBOL(<w>).
      WRITE: / <w>-line.
    ENDLOOP.
  ENDLOOP.

Links

[ABAP] Interne Tabellen: Schleifen mit FOR, THEN, WHILE, GROUPS, IN GROUP

Variante 1 (FOR … WHILE)

DATA(it_strings) = VALUE stringtab( ).

it_strings = VALUE #(
* For i = 1 To 10
                      FOR i = 1 WHILE i < 11
                      (
                        |{ i }|
                      )
                    ).

cl_demo_output=>display( it_strings ).

Variante 2 (FOR … WHILE mit Schrittweite) 

                        
DATA(it_strings) = VALUE stringtab( ).

it_strings = VALUE #(
* For i = 1 To 10 Step 2
                      FOR i = 1 THEN i + 2 WHILE i < 11
                      (
                        |{ i }|
                      )
                    ).

cl_demo_output=>display( it_strings ).

Variante 3 (NESTED FOR)

                        
SELECT * FROM sflight INTO TABLE @DATA(it_sflight).
SELECT * FROM spfli INTO TABLE @DATA(it_spfli).

DATA(it_strings) = VALUE stringtab(
* alle Einträge aus sflight ab 01.01.2013
                                    FOR <f> IN it_sflight INDEX INTO idxf WHERE ( fldate >= '20130101' )
* alle Einträge aus spfli mit den Schlüsseln wie in der Ergebnismenge des vorherigen FOR
                                      FOR <c> IN it_spfli INDEX INTO idxc WHERE ( carrid = <f>-carrid AND connid = <f>-connid )
                                      (
* Ausgabe als Stringtab
                                        |{ idxf } \| { idxc } \| { <c>-carrid } \| { <c>-connid } \| { <c>-airpfrom }|
                                      )
                                  ).

cl_demo_output=>display( it_strings ).

Variante 4 (FOR GROUPS, FOR … IN GROUP)

TYPES: BEGIN OF ty_mat,
         matnr TYPE matnr,
         mtart TYPE mtart,
         price TYPE kbetr,
       END OF ty_mat.

TYPES: ty_it_mat TYPE HASHED TABLE OF ty_mat WITH UNIQUE KEY matnr
                                             WITH NON-UNIQUE SORTED KEY key_mtart COMPONENTS mtart.

TYPES: BEGIN OF ty_mat_sum,
         mtart TYPE mtart,
         count TYPE i,
         price TYPE kbetr,
       END OF ty_mat_sum.

TYPES: ty_it_mat_sum TYPE HASHED TABLE OF ty_mat_sum WITH UNIQUE KEY mtart.

* Tabelle mit Materialien
DATA(it_mat) = VALUE ty_it_mat(
                                ( matnr = '1' mtart = 'ROH'  price = '1.56' )
                                ( matnr = '2' mtart = 'ROH'  price = '2.00' )
                                ( matnr = '3' mtart = 'NLAG' price = '3.10' )
                                ( matnr = '4' mtart = 'NLAG' price = '0.40' )
                                ( matnr = '5' mtart = 'NLAG' price = '4.10' )
                                ( matnr = '6' mtart = 'HALB' price = '1.00' )
                                ( matnr = '7' mtart = 'HALB' price = '0.10' )
                              ).

* Tabelle gruppiert nach Materialarten ohne 'HALB' und summierten Preisen
DATA(it_mat_sum) = VALUE ty_it_mat_sum(
                                        FOR GROUPS grp OF <mtart> IN it_mat WHERE ( mtart NE 'HALB' ) GROUP BY ( mtart = <mtart>-mtart size = GROUP SIZE )
                                        (
                                          mtart = grp-mtart " Materialart der Gruppe
                                          count = grp-size  " Anz. Elemente der Gruppe
                                          price = REDUCE #( " Summe über die Elemente der akt. Gruppe bilden
                                                            INIT p = '0.00'
                                                            FOR <m> IN GROUP grp WHERE ( mtart = grp-mtart )
                                                            NEXT p = p + <m>-price
                                                          )
                                        )
                                      ).

cl_demo_output=>display( it_mat_sum ).

Links

[CDS-Views] CDS-View mit Parametern erstellen und anzeigen

CDS-View als DDIC Objekt erstellen

@AbapCatalog.sqlViewName: 'ZCDS_MATNR_VIEW'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'MATNR lesen'
@Metadata.allowExtensions: true

define view ZCDS_MATNR
  with parameters p_min_date : dats,
                  p_max_date : dats
  as select from mara as ma
  inner join makt as kt on ma.matnr = kt.matnr
{
  key ma.matnr as matnr,
  
  ma.ernam as ersteller,
  
  ma.vpsta as pflegestatus,
  
  case ma.lvorm
    when 'X' then 'gelöscht'
    else 'aktiv'
  end as loeschvormerkung,
  
  kt.maktx as kurztext
  
} where ma.ernam like 'A%' and ma.ersda between :p_min_date and :p_max_date;

ABAP-Quellcode zum Auslesen des CDS-Views

DATA: it_matnr TYPE STANDARD TABLE OF zcds_matnr.

SELECT * FROM zcds_matnr( p_min_date = '20170101', p_max_date = '20171231' ) INTO TABLE @it_matnr.

LOOP AT it_matnr ASSIGNING FIELD-SYMBOL(<mat>).
  WRITE: / <mat>-matnr, <mat>-ersteller, <mat>-pflegestatus, <mat>-loeschvormerkung, <mat>-kurztext.
ENDLOOP.

[ABAP] Open SQL: 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] Open SQL: 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] Tabelleninhalt einer internen Tabelle anhand eines Kriteriums selektieren und kopieren (FILTER, VALUE, FOR, WHERE)

Variante 1 (VALUE, FOR, WHERE) – explizite Feldangabe

* Standardtabelle mit sortiertem Schlüssel carr_city definieren
TYPES: ty_it_spfli TYPE STANDARD TABLE OF spfli WITH DEFAULT KEY.

DATA: it_spfli TYPE ty_it_spfli.
DATA: it_for_spfli TYPE ty_it_spfli.

START-OF-SELECTION.

  SELECT * INTO TABLE it_spfli FROM spfli.

* Tabelleninhalt für Einträge mit carrid = 'LH' selektieren
* und mittels VALUE und FOR kopieren
  it_for_spfli = VALUE #( FOR l IN it_spfli WHERE ( carrid = 'LH' )
                          ( mandt = l-mandt
                            carrid = l-carrid
                            connid = l-connid
                            countryfr = l-countryfr
                            cityfrom = l-cityfrom
                            airpfrom = l-airpfrom
                            countryto = l-countryto
                            cityto = l-cityto
                            airpto = l-airpto
                            fltime = l-fltime
                            deptime = l-deptime
                            arrtime = l-arrtime
                            distance = l-distance
                            distid = l-distid
                            fltype = l-fltype
                            period = l-period ) ).

  LOOP AT it_for_spfli ASSIGNING FIELD-SYMBOL(<fs_l>).
    WRITE: / <fs_l>-carrid, <fs_l>-cityfrom, <fs_l>-deptime.
  ENDLOOP.

Variante 2 (VALUE, FOR, WHERE) – alle Felder mit Variable

* Standardtabelle mit sortiertem Schlüssel carr_city definieren
TYPES: ty_it_spfli TYPE STANDARD TABLE OF spfli WITH DEFAULT KEY.

DATA: it_spfli TYPE ty_it_spfli.
DATA: it_for_spfli TYPE ty_it_spfli.

START-OF-SELECTION.

  SELECT * INTO TABLE it_spfli FROM spfli.

* Tabelleninhalt für Einträge mit carrid = 'LH' selektieren
* und mittels VALUE und FOR kopieren
* ( l ) -> komplette Zeile
  it_for_spfli = VALUE #( FOR l IN it_spfli WHERE ( carrid = 'LH' )
                          ( l ) ).

  LOOP AT it_for_spfli ASSIGNING FIELD-SYMBOL(<fs_l>).
    WRITE: / <fs_l>-carrid, <fs_l>-cityfrom, <fs_l>-deptime.
  ENDLOOP.

Variante 3 (VALUE, FOR, WHERE) – alle Felder mit Feldsymbol

* Standardtabelle mit sortiertem Schlüssel carr_city definieren
TYPES: ty_it_spfli TYPE STANDARD TABLE OF spfli WITH DEFAULT KEY.

DATA: it_spfli TYPE ty_it_spfli.
DATA: it_for_spfli TYPE ty_it_spfli.

START-OF-SELECTION.

  SELECT * INTO TABLE it_spfli FROM spfli.

* Tabelleninhalt für Einträge mit carrid = 'LH' selektieren
* und mittels VALUE und FOR kopieren
* ( l ) -> komplette Zeile
  it_for_spfli = VALUE #( FOR <fs> IN it_spfli WHERE ( carrid = 'LH' )
                          ( <fs> ) ).

  LOOP AT it_for_spfli ASSIGNING FIELD-SYMBOL(<fs_l>).
    WRITE: / <fs_l>-carrid, <fs_l>-cityfrom, <fs_l>-deptime.
  ENDLOOP.

Variante 4 (FILTER, KEY, WHERE)

* Standardtabellem mit sortiertem Schlüssel
TYPES: ty_it_spfli TYPE STANDARD TABLE OF spfli WITH NON-UNIQUE SORTED KEY carr_city COMPONENTS carrid cityfrom.

DATA: it_spfli TYPE ty_it_spfli.
DATA: it_filter_spfli TYPE ty_it_spfli.

START-OF-SELECTION.

  SELECT * INTO TABLE it_spfli FROM spfli.

* Tabelleninhalt für Einträge mit carrid = 'LH' mittels Filter selektieren und kopieren
* Daten sind anhand des Schlüssels vorsortiert
  it_filter_spfli = FILTER #( it_spfli USING KEY carr_city WHERE carrid = CONV #( 'LH' ) ).

  LOOP AT it_filter_spfli ASSIGNING FIELD-SYMBOL(<fs_l>).
    WRITE: / <fs_l>-carrid, <fs_l>-cityfrom, <fs_l>-deptime.
  ENDLOOP.

[ABAP] Datensätze einer internen Tabelle mit FOR … WHERE kopieren 

TYPES: ty_spfli TYPE STANDARD TABLE OF spfli WITH DEFAULT KEY.

DATA: it_spfli TYPE ty_spfli.

SELECT * FROM spfli INTO TABLE @it_spfli.

* Daten mit carrid = LH in neue iTab kopieren
* es werden nur die Daten in carrid, connid, cityfrom, cityto übertragen
DATA(it_for) = VALUE ty_spfli( FOR l IN it_spfli WHERE ( carrid = 'LH' )
                               ( carrid = l-carrid
                                 connid = l-connid
                                 cityfrom = l-cityfrom
                                 cityto = l-cityto ) ).

[ABAP] Daten aus einer internen Tabelle löschen

Typen & Daten

* Typen
TYPES : BEGIN OF ty_s_sflight,
          carrid   TYPE sflight-carrid,
          connid   TYPE sflight-connid,
          seatsmax TYPE sflight-seatsmax,
          flag     TYPE abap_bool,
        END OF ty_s_sflight.

TYPES: ty_it_sflight TYPE STANDARD TABLE OF ty_s_sflight WITH DEFAULT KEY.

* Daten
DATA(it_sflight) = VALUE ty_it_sflight( ).

* Select
SELECT carrid,
       connid,
       seatsmax,
       CASE WHEN seatsmax > 200 THEN @abap_true " CASE-Anweisung zum Setzen der Spalte "flag"
       END AS flag
  INTO TABLE @it_sflight
  FROM sflight.

Variante 1 (INDEX)

* Eintrag mit Index 1
DELETE it_sflight INDEX 1.

cl_demo_output=>display( it_sflight ).

Variante 2 (Struktur)

* Genau einen Eintrag entsprechend der Struktur
DELETE TABLE it_sflight FROM VALUE #( carrid = 'LH' connid = '400' seatsmax = 280 flag = abap_true ).

cl_demo_output=>display( it_sflight ).

Variante 3 (TABLE KEY)

* Einträge mit Primärschlüssel aus Tabelle löschen
DELETE TABLE it_sflight WITH TABLE KEY carrid = 'LH' connid = '400' flag = abap_true.

cl_demo_output=>display( it_sflight ).

Variante 4 (WHERE)

* Alle Einträge mit leerem flag löschen
DELETE it_sflight WHERE flag IS INITIAL.
* Alle Einträge innerhalb eines Bereiches löschen
DELETE it_sflight WHERE seatsmax > 200 AND seatsmax < 350.
* Alle Einträge die nicht der Bedingung entsprechen
DELETE it_sflight WHERE NOT carrid = 'AA'.

cl_demo_output=>display( it_sflight ).

Variante 5 (RANGE)

* alle Carrier, die nicht 'AA' sind aus der iTab löschen
DELETE it_sflight WHERE NOT carrid IN VALUE rseloption( ( sign   = 'I'
                                                          option = 'EQ'
                                                          low    = 'AA'
                                                          high   = '' ) ).

cl_demo_output=>display( it_sflight ).

Variante 6 (RANGE mit Pattern)

* Alle Einträge beginnend mit 'A' und 'L' löschen
DELETE it_sflight WHERE carrid IN VALUE rseloption( ( sign   = 'I'
                                                      option = 'CP'
                                                      low    = 'A*'
                                                      high   = '' )
                                                    ( sign   = 'I'
                                                      option = 'CP'
                                                      low    = 'L*'
                                                      high   = '' ) ).

cl_demo_output=>display( it_sflight ).

Variante 7 (RANGE leer)

* Achtung: löscht alle Einträge in it_sflight!
DELETE it_sflight WHERE carrid IN VALUE rseloption( ).

cl_demo_output=>display( it_sflight ).

Variante 8 (Pattern CP)

* alle Einträge, deren carrid mit 'L' beginnen
DELETE it_sflight WHERE carrid CP 'L*'.

cl_demo_output=>display( it_sflight ).

Variante 9 (dynamische WHERE-Condition)

* Stringtab mit WHERE-Conditions -> Leerzeichen beachten!
DATA(it_where) = VALUE stringtab(
                                  ( |testfehler = 'AA'| )
                                  ( |carrid EQ 'AA' AND connid CP '001*' | )
                                  ( |seatsmax > 300| )
                                ).

LOOP AT it_where ASSIGNING FIELD-SYMBOL(<w>).

  WRITE: / <w>.

  TRY.
* dynamische WHERE-Condition ausführen
* fehlerhafte WHERE-Conditions werfen eine Exception
      DELETE it_sflight WHERE (<w>).
    CATCH cx_root INTO DATA(e_txt).
      WRITE: / e_txt->get_text( ).
  ENDTRY.

ENDLOOP.

cl_demo_output=>display( it_sflight ).

Variante 10 (Pseudokomponente TABLE_LINE)

* Die Tabellenzeile mit der Struktur ty_s_sflight wird gelöscht
DELETE it_sflight WHERE table_line = VALUE ty_s_sflight( carrid = 'LH' connid = '400' seatsmax = 280 flag = abap_true ).

cl_demo_output=>display( it_sflight ).

Variante 11 (CO [Contains Only])

* Carrier darf nur Zeichen 'A' und/oder 'Z' und/oder ' ' enthalten
* -> löscht Einträge für 'AA ', 'AZ '
DELETE it_sflight WHERE carrid CO 'AZ '.

cl_demo_output=>display( it_sflight ).