Startseite SAP ABAP Archive nach Kategorien "SQL" (Seite 5)

Kategorie: SQL

[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] Letzter Loginzeitpunkt eines Users 

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

SELECT * FROM usr02 INTO TABLE it_usr UP TO 100 ROWS.

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] 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] DB-Tabelle sperren (lock / unlock) 

* Lock für Table SCARR setzen
* Siehe auch Transaktion SM12
CALL FUNCTION 'ENQUEUE_E_TABLE'
  EXPORTING
    mode_rstable   = 'E'
    tabname        = 'SCARR'
  EXCEPTIONS
    foreign_lock   = 1
    system_failure = 2
    OTHERS         = 3.

IF sy-subrc = 0.
* Wenn Lock gesetzt, dann
*  UPDATE scarr FROM TABLE ...
  IF sy-subrc = 0.
    WRITE: / 'Update ok.'.
  ELSE.
    WRITE: / 'Fehler beim Update.'.
  ENDIF.

* Lock für Table SCARR aufheben
  CALL FUNCTION 'DEQUEUE_E_TABLE'
    EXPORTING
      mode_rstable = 'E'
      tabname      = 'SCARR'.
ELSE.
  WRITE: / 'Lock konnte nicht gesetzt werden.'.
ENDIF.

[ABAP] Datensatz sperren (lock / unlock) 

DATA: lv_ds TYPE sflight.

* Lock für Datensatz setzen
SELECT SINGLE FOR UPDATE * FROM sflight INTO @lv_ds
  WHERE carrid = 'AA'
    AND connid = '0017'
    AND fldate = '20050928'.

IF sy-subrc = 0.
* Wenn Lock gesetzt, dann
*  UPDATE sflight SET ...
  IF sy-subrc = 0.
    COMMIT WORK.
  ELSE.
    ROLLBACK WORK.
  ENDIF.
ELSE.
  WRITE: / 'Lock konnte nicht gesetzt werden.'.
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 ).

[ABAP] User auflisten, die sich in den letzten x Tagen nicht angemeldet haben 

PARAMETERS: p_days TYPE i OBLIGATORY DEFAULT '90'.

START-OF-SELECTION.

  DATA: last_date TYPE d.
  DATA: it_users TYPE STANDARD TABLE OF usr02 WITH DEFAULT KEY.

  DATA(lv_erdat) = sy-datum.
  lv_erdat = lv_erdat - p_days.

* Dialognutzer ('A') lesen, die sich in den letzten x Tagen nicht angemeldet haben
  SELECT * FROM usr02 INTO TABLE @it_users
    WHERE ustyp = 'A'
      AND trdat = '00000000'
      AND erdat <= @lv_erdat.

  IF sy-subrc = 0.

    SORT: it_users BY bname.

    WRITE: / |USER         \| LAST LOGIN \| CREATED|.

    ULINE.

    LOOP AT it_users ASSIGNING FIELD-SYMBOL(<u>).
      WRITE: / <u>-bname, '|', <u>-trdat, '|', <u>-erdat.
    ENDLOOP.

  ENDIF.

[ABAP] Prüfen, ob Datensatz auf der Datenbank vorhanden 

DATA: lv_matnr_ok TYPE boolean VALUE abap_false.

PARAMETERS: p_matnr TYPE mara-matnr.

* wenn Datensatz vorhanden, wird abap_true in lv_matnr_ok geschrieben
SELECT SINGLE @abap_true FROM mara INTO @lv_matnr_ok
  WHERE matnr = @p_matnr.

WRITE: / COND string( WHEN lv_matnr_ok = abap_true THEN 'MATNR vorhanden.' ELSE 'MATNR nicht vorhanden.').

IF sy-subrc NE 0.
  WRITE: / 'Fehler:', sy-subrc.
ENDIF.