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] Wildcards (%) im SELECT-Statement verwenden

Beispiel 1: PARAMETERS mit Wildcard (%)

DATA: it_spfli TYPE STANDARD TABLE OF spfli.

PARAMETERS: p_carr TYPE spfli-carrid DEFAULT '%'.
  
* SELECT mit LIKE, für % als Wildcard
SELECT * FROM spfli
  INTO TABLE @it_spfli
  WHERE carrid LIKE @p_carr.

Beispiel 2: WHERE-Clause mit Wildcard (%)

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

* alle Namen, die mit "Z" beginnen
SELECT * FROM usr02 INTO TABLE it_usr WHERE bname LIKE 'Z%'.

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