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

Kategorie: SQL

[ABAP] Prüfen des Datenenbanktyps, prüfen auf HANA DB

Variante 1 (cl_db_sys)

*ANY       Keine DB-spezifischen Optimierungen verwenden (nur Open SQL)
*HDB       HDB
*DB2       DB2
*DB400     DB4
*DB6       DB6
*ADABAS D  MaxDB
*INFORMIX  Informix
*MSSQL     MSSQL
*MYSQL     MYSQL
*ORACLE    Oracle
*SYBASE    Sybase
WRITE: / 'dbsys_type:', cl_db_sys=>dbsys_type.
WRITE: / 'is_in_memory_db:', cl_db_sys=>is_in_memory_db.

Variante 2 (cl_swnc_settings)

WRITE: / 'is_hana_on:', cl_swnc_settings=>is_hana_on( ).
WRITE: / 'are_hana_features_on:', cl_swnc_settings=>are_hana_features_on( ).

[ABAP] Data Aging: DB-Zugriffssteuerung für S/4HANA Data Aging

Variante 1 (cl_abap_stack_temperature)

* https://blogs.sap.com/2016/10/03/how-to-perform-data-aging-in-s4hana/
* Transaktion: DAGOBJ
*              DAGPTC

* Flag für Historische Daten
DATA(lv_cold_access) = abap_true.

* Datum für Aging
DATA: lv_data_temperature TYPE data_temperature.
lv_data_temperature = sy-datum - 30.

TRY.
* data aging möglich (von db unterstützt und aktivgeschaltet)?
    IF abap_true = cl_data_aging_state=>is_enabled(  ).
      IF abap_true = lv_cold_access.
* datenfilter setzen: alle daten sichtbar
        cl_abap_stack_temperature=>set_cold( ).
      ELSE.
        IF lv_data_temperature IS INITIAL.
* datenfilter setzen: nur hot-daten sichtbar
          cl_abap_stack_temperature=>set_hot( ).
        ELSE.
          cl_abap_stack_temperature=>set_temperature( lv_data_temperature ).
        ENDIF.
      ENDIF.
    ENDIF.
  CATCH cx_root INTO DATA(e_txt).
    WRITE: e_txt->get_text( ).
ENDTRY.

Variante 2 (cl_abap_session_temperature)

* https://blogs.sap.com/2016/10/03/how-to-perform-data-aging-in-s4hana/
* Transaktion: DAGOBJ
*              DAGPTC

* Flag für Historische Daten
DATA(lv_cold_access) = abap_true.

* Datum für Aging
DATA: lv_data_temperature TYPE data_temperature.
lv_data_temperature = sy-datum - 30.

TRY.
    DATA(o_ses) = cl_abap_session_temperature=>get_session_control( ).
* Data Aging möglich (von DB unterstützt und aktivgeschaltet)?
    IF abap_true = cl_data_aging_state=>is_enabled(  ).
      IF abap_true = lv_cold_access.
* Datenfilter setzen: Alle Daten sichtbar
        o_ses->set_cold( ).
      ELSE.
        IF lv_data_temperature IS INITIAL.
* Datenfilter setzen: Nur HOT-Daten sichtbar
          o_ses->set_hot( ).
        ELSE.
          o_ses->set_temperature( lv_data_temperature ).
        ENDIF.
      ENDIF.
    ENDIF.
  CATCH cx_root INTO DATA(e_txt).
    WRITE: e_txt->get_text( ).
ENDTRY.

[ABAP] OpenSQL: Unterschied SELECT, SELECT SINGLE, SELECT DISTINCT

SELECT

* Selektiert alle Einträge für 'DE' und 'US', mit Mehrfachnennungen
SELECT cityto
  INTO TABLE @DATA(it_dest)
  FROM spfli
  WHERE countryfr = 'DE'
     OR countryfr = 'US'.

*CITYTO:
*SAN FRANCISCO
*NEW YORK
*FRANKFURT
*SAN FRANCISCO
*NEW YORK
*TOKYO
*NEW YORK
*FRANKFURT
*NEW YORK
*BERLIN
*FRANKFURT
*SINGAPORE
*SINGAPORE
*SAN FRANCISCO
*FRANKFURT
*FRANKFURT
*NEW YORK

cl_demo_output=>display_data( it_dest ).

SELECT DISTINCT

* Selektiert alle Einträge für 'DE' und 'US', ohne Mehrfachnennungen
SELECT DISTINCT cityto
  INTO TABLE @DATA(it_dest)
  FROM spfli
  WHERE countryfr = 'DE'
     OR countryfr = 'US'.

*CITYTO:
*TOKYO
*BERLIN
*NEW YORK
*SINGAPORE
*FRANKFURT
*SAN FRANCISCO
cl_demo_output=>display_data( it_dest ).

SELECT SINGLE

* Selektiert den ersten Datensatz für 'DE' und 'US'
SELECT SINGLE cityto
  INTO @DATA(lv_dest)
  FROM spfli
  WHERE countryfr = 'DE'
     OR countryfr = 'US'.

*CITYTO:
*SAN FRANCISCO
cl_demo_output=>display_data( lv_dest ).

[ABAP] OpenSQL: Zusammenführen von Tabelleninhalten mit SELECT UNION

Variante 1 (UNION == UNION DISTINCT – einfaches Auftreten der Datensätze)

DATA: lv_devclass TYPE string VALUE 'ZABAP2XLSX'.

SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'PROG' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'CLAS' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'FUGR' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'DEVC' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'DOMA' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'DTEL' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'INTF' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'MSAG' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'TABL' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'TTYP' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'WDYA' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'XSLT' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'WDYN' AND devclass = @lv_devclass
 ORDER BY obj_name
 INTO TABLE @DATA(it_prog_range).

DATA(lv_lines) = lines( it_prog_range ).
cl_demo_output=>write_data( lv_lines ).
cl_demo_output=>write_data( it_prog_range ).
cl_demo_output=>display( ).

Variante 2 (UNION ALL – mehrfach auftretende Datensätze)

DATA: lv_devclass TYPE string VALUE 'ZABAP2XLSX'.

SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'PROG' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'CLAS' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'FUGR' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'DEVC' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'DOMA' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'DTEL' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'INTF' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'MSAG' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'TABL' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'TTYP' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'WDYA' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'XSLT' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'WDYN' AND devclass = @lv_devclass
 ORDER BY obj_name
 INTO TABLE @DATA(it_prog_range).

DATA(lv_lines) = lines( it_prog_range ).
cl_demo_output=>write_data( lv_lines ).
cl_demo_output=>write_data( it_prog_range ).
cl_demo_output=>display( ).

Links

[ABAP] Datensätze aus einer internen Tabelle zu einer DB-Tabelle hinzufügen

  • COMMIT WORK wird am Programmende automatisch ausgeführt
  • Aktionen können mit ROLLBACK rückgängig gemacht werden

Variante 1 (einzelner Datensatz)

* Datensatz
DATA(lv_struct) = VALUE zxyz( col1 = 'Value1' col1 = 'Value2' ).

INSERT INTO zxyz VALUES @lv_struct.
IF sy-subrc = 0.
  WRITE : / 'Hinzufügen erfolgreich.'.
ELSE.
  WRITE : / 'Datensatz konnte nicht hinzugefügt werden.'.
ENDIF.

Variante 2 (mehrere Datensätze aus einer itab)

* itab mit Datensätzen
DATA(itab) = VALUE zxyz_tab(
                             ( col1 = 'Value1' col1 = 'Value2' )
                             ( col1 = 'Value3' col1 = 'Value4' )
                           ).
* Wenn der Zusatz ACCEPTING DUPLICATE KEYS angegeben ist, werden alle Zeilen eingefügt, für die dies möglich ist.
* Die restlichen Zeilen werden verworfen und sy-subrc wird auf 4 gesetzt.
INSERT zxyz FROM TABLE itab ACCEPTING DUPLICATE KEYS.

IF sy-subrc = 0.
  WRITE : / 'Hinzufügen erfolgreich.'.
ELSE.
  WRITE : / 'Nicht alle Zeilen der Tabelle itab wurden hinzugefügt.'.
ENDIF.

* sy-dbcnt = Anzahl der eingefügten Zeilen
WRITE: / 'Hinzugefügte Datensätze:', sy-dbcnt.

[ABAP] Native SQL: Case-Insensitive Suche mit SELECT … UPPER 

TYPES: BEGIN OF ty_s_tableline,
         matnr TYPE matnr,
         spras TYPE spras,
         maktx TYPE maktx,
       END OF ty_s_tableline.

TYPES: ty_it_table TYPE STANDARD TABLE OF ty_s_tableline WITH DEFAULT KEY.

START-OF-SELECTION.

  TRY.
* Open Cursor and SELECT mit UPPER-CASE-Funktion
* ist im Standard-OpenSQL nicht möglich
      EXEC SQL.
        OPEN dbcur FOR
        SELECT matnr,
               spras,
               maktx
        FROM makt
        WHERE spras = 'D'
          AND UPPER(maktx) LIKE 'SCH%'
      ENDEXEC.

      IF sy-subrc = 0.
        DATA: lv_line TYPE ty_s_tableline.
        DATA: it_table TYPE ty_it_table.

* Loop Cursor Data
        DO.
* DB-Cursor auf nächsten Datensatz setzen, solange, wie Daten vorhanden sind
          EXEC SQL.
            FETCH NEXT dbcur INTO :lv_line
          ENDEXEC.
          IF sy-subrc = 0.
* Daten an itab anfügen
            APPEND lv_line TO it_table.
          ELSE.
            EXIT.
          ENDIF.
        ENDDO.

* Close Cursor
        EXEC SQL.
          CLOSE dbcur
        ENDEXEC.

* Daten ausgeben
        cl_demo_output=>display( it_table ).
      ENDIF.

    CATCH cx_sy_native_sql_error INTO DATA(e_txt).
      WRITE: / e_txt->get_text( ).
  ENDTRY.

[ABAP] Native SQL-Zugriff auf eine Oracle-Datenbank 

* DBCO: Transaktion zur Pflege der Datenbankverbindung zur Oracle-DB
* DBACOCKPIT
* DBCON: Tabelle der Datenbankverbindungen

* http://www.saptechnical.com/Tutorials/Basis/Oracle/RetrieveData.htm
* http://sqlines.com/oracle-to-sql-server/to_char_datetime
* https://archive.sap.com/discussions/thread/33542
* https://archive.sap.com/discussions/thread/107384
* https://blogs.sap.com/2012/12/28/native-sql-its-use-with-database-connection-in-sap/
* https://help.sap.com/http.svc/rc/abapdocu_750_index_htm/7.50/de-DE/abapexec_connection.htm

* Beispieldatentyp für verschiedene Oracle-Datentypen
TYPES: BEGIN OF ty_s_tableline,
         col_number     TYPE i,                      " NUMBER
         col_number10_2 TYPE p LENGTH 10 DECIMALS 2, " NUMBER (10,2)
         col_number5    TYPE p LENGTH 5 DECIMALS 0,  " NUMBER (5)
         col_char       TYPE c LENGTH 16,            " VARCHAR2 (16 Char)
         col_date_conv  TYPE c LENGTH 19,            " DATE ('YYYY-MM-DD HH24:MI:SS')
         col_date_conv2 TYPE c LENGTH 15,            " DATE ('YYYYMMDD HH24MISS')
       END OF ty_s_tableline.

TYPES: ty_it_table TYPE STANDARD TABLE OF ty_s_tableline WITH DEFAULT KEY.

* DB Connection zur Oracle DB
PARAMETERS: p_dbs TYPE dbcon-con_name DEFAULT 'MYDBCON'.
PARAMETERS: p_par1 TYPE matnr DEFAULT '12345'.

START-OF-SELECTION.

* Verbindungsinfos zur DB Connection lesen
  DATA: lv_dbtype TYPE dbcon_dbms.

  SELECT SINGLE dbms
    INTO @lv_dbtype
    FROM dbcon
    WHERE con_name = @p_dbs.

  IF sy-subrc = 0.
* Repräsentiert die Connection eine Oracle-DB?
    IF lv_dbtype = 'ORA'. " Domäne DBCON_DBMS

      TRY.
* Prüfen, ob die DB-Connection schon offen ist
          EXEC SQL.
            SET CONNECTION :p_dbs
          ENDEXEC.

          IF sy-subrc <> 0.
* Connect to DB
            EXEC SQL.
              CONNECT TO :p_dbs
            ENDEXEC.
          ENDIF.

          IF sy-subrc = 0.
* Session-Parameter setzen
            EXEC SQL.
              alter session set NLS_DATE_FORMAT = 'YYYYMMDD'
            ENDEXEC.

            EXEC SQL.
              alter session set NLS_NUMERIC_CHARACTERS = ',.'
            ENDEXEC.

* Open Cursor and SELECT
* SQL-Statements im ORACLE Syntax!
* Abbildung verschiedener Oracle-Datentypen als ABAP-Datentypen
* speziell das Datum (Orycle Typ DATE) muss Oracleseitig als Character-Feld (z.B. Länge 19 oder 15) interpretiert werden (CAST), damit es korrekt an SAP übertragen werden kann
* zusätzliche Komplexität durch INNER JOIN von zwei Tabellen mytab und mytab2
* Übergabe von Parametern an die EXEC SQL-Bereiche erfolgt immer durch ":Variablenname"
            EXEC SQL.
              OPEN dbcur FOR
              SELECT mytab.col_number,
                     mytab.col_number10_2,
                     mytab2.col_number5,
                     mytab.col_char,
                     CAST (TO_CHAR (mytab.col_date, 'YYYY-MM-DD HH24:MI:SS') AS VARCHAR2 (19)) AS col_date_conv
                     CAST (TO_CHAR (mytab.col_date, 'YYYYMMDD HH24MISS') AS VARCHAR2 (15)) AS col_date_conv2
              FROM myschemaname.mytablename mytab
              INNER JOIN myschemaname.mytablename2 mytab2 ON mytab.key1 = mytab2.key1 AND mytab.key2 = mytab2.key2
              WHERE mytab2.colxyz = :p_par1
            ENDEXEC.

            IF sy-subrc = 0.
              DATA: lv_line TYPE ty_s_tableline.
              DATA: it_table TYPE ty_it_table.

* Loop Cursor Data
              DO.
* DB-Cursor auf nächsten Datensatz setzen, solange, wie Daten vorhanden sind
                EXEC SQL.
                  FETCH NEXT dbcur INTO :lv_line
                ENDEXEC.
                IF sy-subrc = 0.
* Daten an itab anfügen
                  APPEND lv_line TO it_table.
                ELSE.
* keine Daten mehr vorhanden (EOF)
                  EXIT.
                ENDIF.
              ENDDO.

* Close Cursor
              EXEC SQL.
                CLOSE dbcur
              ENDEXEC.

            ENDIF.

* Reset auf "default connection"
            EXEC SQL.
              SET CONNECTION DEFAULT
            ENDEXEC.

* Disconnect
            EXEC SQL.
              DISCONNECT :p_dbs
            ENDEXEC.

* Daten ausgeben
            DATA: o_salv TYPE REF TO cl_salv_table.

            cl_salv_table=>factory( IMPORTING
                                      r_salv_table = o_salv
                                    CHANGING
                                      t_table      = it_table ).

            o_salv->get_functions( )->set_all( abap_true ).
            o_salv->get_columns( )->set_optimize( abap_true ).
            o_salv->get_display_settings( )->set_list_header( 'Oracle Data' ).
            o_salv->get_display_settings( )->set_striped_pattern( abap_true ).
            o_salv->get_selections( )->set_selection_mode( if_salv_c_selection_mode=>row_column ).

            LOOP AT o_salv->get_columns( )->get( ) ASSIGNING FIELD-SYMBOL(<c>).
              <c>-r_column->set_short_text( |{ <c>-r_column->get_columnname( ) }| ).
              <c>-r_column->set_medium_text( |{ <c>-r_column->get_columnname( ) }| ).
              <c>-r_column->set_long_text( |{ <c>-r_column->get_columnname( ) }| ).
            ENDLOOP.

            o_salv->display( ).
          ELSE.
            WRITE: / 'DB-Verbindung konnte nicht hergestellt werden.'.
          ENDIF.
        CATCH cx_root INTO DATA(e_txt).
          WRITE: / e_txt->get_text( ).
      ENDTRY.

    ELSE.
      WRITE: / |Falscher Datenbanktyp: { lv_dbtype }|.
    ENDIF.
  ELSE.
    WRITE: / |DB-Alias { p_dbs } nicht im System vorhanden.|.
  ENDIF.