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

Kategorie: SQL

[ABAP] AUTHORITY-CHECK – Objekte in OpenSQL-WHERE-Condition wandeln 

* ab ABAP 7.50

* Koverter-Objekt erzeugen
DATA(o_auth) = cl_auth_objects_to_sql=>create_for_open_sql( ).

* Objekte für AUTHORITY-CHECK hinzufügen
o_auth->add_authorization_object( iv_authorization_object = 'S_CARRID'
                                  it_activities = VALUE #( ( auth_field = 'ACTVT' value = '03' ) )
                                  it_field_mapping = VALUE #( ( auth_field = 'CARRID'
                                                                view_field = VALUE #( table_ddic_name = 'SFLIGHT'
                                                                                      field_name      = 'CARRID'
                                                                                    )
                                                              )
                                                            )
                                ).

* Ist der Benutzer berechtigt?
IF abap_true = o_auth->is_authorized( ).

* WHERE-Condition erzeugen
  DATA(lv_where_cond) = o_auth->get_sql_condition( ).

* Wenn leer, dann hat der Benutzer alle Berechtigungen
  IF lv_where_cond IS INITIAL.
    cl_demo_output=>write_data( 'Alle Berechtigungen.' ).
  ELSE.
* Ansonsten eingeschränkte Berechtigungen
    cl_demo_output=>write_data( |Eingeschränkte Berechtigungen: { lv_where_cond }| ).
  ENDIF.

* SELECT mit WHERE-Condition durchführen
  SELECT *
    INTO TABLE @DATA(it_sflight)
    FROM sflight
    WHERE (lv_where_cond).

* Datenausgabe
  cl_demo_output=>write_data( it_sflight ).
  cl_demo_output=>display( ).

ENDIF.

Links

[ABAP] Verarbeitungsstatus von Nachrichten aus der Tabelle NAST lesen 

* Tabelle: T681A (Konditionen: Applikationen)
PARAMETERS: p_kappl TYPE t681a-kappl DEFAULT 'V2'.

* Tabelle: T685 (Konditionen: Arten)
PARAMETERS: p_kschl TYPE t685-kschl DEFAULT 'LAVA'.

DATA(lv_vstat) = VALUE nast-vstat( ).

* Domäne NA_VSTAT:
* 0 - nicht verarbeitet
* 1 - erfolgreich verarbeitet
* 2 - fehlerhaft verarbeitet
SELECT-OPTIONS: so_vstat FOR lv_vstat.

INITIALIZATION.

* Verarbeitungssstatus der Nachricht vorbelegen
  so_vstat[] = VALUE #( ( sign   = 'I'
                          option = 'EQ'
                          low    = '1'  " 1 - erfolgreich verarbeitet
                          high   = ''
                        ) ).

START-OF-SELECTION.

* Nachrichtenstatus lesen
  SELECT *
    INTO TABLE @DATA(it_nast)
    FROM nast
    WHERE kappl = @p_kappl
      AND kschl = @p_kschl
      AND vstat IN @so_vstat.

  cl_demo_output=>write_data( it_nast ).
  cl_demo_output=>display( ).

[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] Infos zu einem DB-View lesen 

PARAMETERS: p_view TYPE ddobjname DEFAULT 'LIKPUK'.

* Views mit Texten
DATA: lv_dd25v TYPE dd25v.
* DD: Technische Einstellungen von Tabellen
DATA: lv_dd09l TYPE dd09l.

* View auf Basistabellen eines Views aus Tabelle DD26S
DATA: it_dd26v_tab TYPE STANDARD TABLE OF dd26v.
* View auf Felder eines SAP-Tabellenviews aus Tabelle DD27S
DATA: it_dd27p_tab TYPE STANDARD TABLE OF dd27p.
* Interne Struktur für Joins von Views
DATA: it_dd28j_tab TYPE STANDARD TABLE OF dd28j.
* View auf Selektionsbedingung
DATA: it_dd28v_tab TYPE STANDARD TABLE OF dd28v.

CALL FUNCTION 'DDIF_VIEW_GET'
  EXPORTING
    name          = p_view
  IMPORTING
    dd25v_wa      = lv_dd25v
    dd09l_wa      = lv_dd09l
  TABLES
    dd26v_tab     = it_dd26v_tab
    dd27p_tab     = it_dd27p_tab
    dd28j_tab     = it_dd28j_tab
    dd28v_tab     = it_dd28v_tab
  EXCEPTIONS
    illegal_input = 1
    OTHERS        = 2.

IF sy-subrc = 0.
  cl_demo_output=>write_data( lv_dd25v ).
  cl_demo_output=>write_data( lv_dd09l ).
  cl_demo_output=>write_data( it_dd26v_tab ).
  cl_demo_output=>write_data( it_dd27p_tab ).
  cl_demo_output=>write_data( it_dd28j_tab ).
  cl_demo_output=>write_data( it_dd28v_tab ).
  cl_demo_output=>display( ).
ENDIF.

[ABAP] Verfügbare DB-Views zu Tabellen suchen 

TYPES : BEGIN OF ty_s_views,
          viewname  TYPE dd26s-viewname,
          ddtext    TYPE dd25t-ddtext,
          roottab   TYPE dd25l-roottab,
          viewclass TYPE dd02l-viewclass,
          viewgrant TYPE dd02l-viewgrant,
        END OF ty_s_views.

TYPES: ty_it_views TYPE STANDARD TABLE OF ty_s_views WITH DEFAULT KEY.

DATA: it_views TYPE ty_it_views.

CLASS lcl_events DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS: on_double_click FOR EVENT double_click OF cl_salv_events_table
      IMPORTING
          row
          column
          sender.
ENDCLASS.

CLASS lcl_events IMPLEMENTATION.
  METHOD on_double_click.
    DATA(lv_row) = it_views[ row ].
    ASSIGN COMPONENT column OF STRUCTURE lv_row TO FIELD-SYMBOL(<cell>).

* views mit texten
    DATA: lv_dd25v TYPE dd25v.
* DD: Technische Einstellungen von Tabellen
    DATA: lv_dd09l TYPE dd09l.

* View auf Basistabellen eines Views aus Tabelle DD26S
    DATA: it_dd26v_tab TYPE STANDARD TABLE OF dd26v.
* View auf Felder eines SAP-Tabellenviews aus Tabelle DD27S
    DATA: it_dd27p_tab TYPE STANDARD TABLE OF dd27p.
* Interne Struktur für Joins von Views
    DATA: it_dd28j_tab TYPE STANDARD TABLE OF dd28j.
* View auf Selektionsbedingung
    DATA: it_dd28v_tab TYPE STANDARD TABLE OF dd28v.

    CALL FUNCTION 'DDIF_VIEW_GET'
      EXPORTING
        name          = <cell>
      IMPORTING
        dd25v_wa      = lv_dd25v
        dd09l_wa      = lv_dd09l
      TABLES
        dd26v_tab     = it_dd26v_tab
        dd27p_tab     = it_dd27p_tab
        dd28j_tab     = it_dd28j_tab
        dd28v_tab     = it_dd28v_tab
      EXCEPTIONS
        illegal_input = 1
        OTHERS        = 2.

    IF sy-subrc = 0.
      cl_demo_output=>write_data( lv_dd25v ).
      cl_demo_output=>write_data( lv_dd09l ).
      cl_demo_output=>write_data( it_dd26v_tab ).
      cl_demo_output=>write_data( it_dd27p_tab ).
      cl_demo_output=>write_data( it_dd28j_tab ).
      cl_demo_output=>write_data( it_dd28v_tab ).
      cl_demo_output=>display( ).
    ENDIF.
  ENDMETHOD.
ENDCLASS.

DATA: lv_tab TYPE dd26s-fortabname.

SELECT-OPTIONS: so_tabs FOR lv_tab.

INITIALIZATION.

  so_tabs[] = VALUE #(
                       ( sign = 'I' option = 'EQ' low = 'MARA' high = '' )
                       ( sign = 'I' option = 'EQ' low = 'MARC' high = '' )
                     ).

START-OF-SELECTION.

  DATA(lv_lang) = cl_abap_syst=>get_logon_language( ).

  SELECT s~viewname,
         t~ddtext,
         r~roottab,
         l~viewclass,
         l~viewgrant
    INTO TABLE @it_views
    FROM dd26s AS s
    INNER JOIN dd02l AS l ON l~tabname = s~viewname
    INNER JOIN dd25l AS r ON r~viewname = l~tabname
    INNER JOIN dd25t AS t ON t~viewname = l~tabname
    WHERE s~fortabname IN @so_tabs
      AND l~as4local   = 'A'
      AND l~tabclass   = 'VIEW'
      AND t~ddlanguage = @lv_lang.

  IF sy-subrc = 0.

    SORT: it_views BY viewname.

    TRY.
        DATA: o_salv TYPE REF TO cl_salv_table.

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

        LOOP AT o_salv->get_columns( )->get( ) ASSIGNING FIELD-SYMBOL(<c>).
          DATA(o_col) = <c>-r_column.
          o_col->set_short_text( '' ).
          o_col->set_medium_text( '' ).
          o_col->set_long_text( |{ o_col->get_columnname( ) } [{ o_col->get_long_text( ) }]| ).
        ENDLOOP.

        o_salv->get_functions( )->set_all( abap_true ).
        o_salv->get_columns( )->set_optimize( abap_true ).
        o_salv->get_display_settings( )->set_list_header( |Views: { lines( it_views ) }| ).
        o_salv->get_display_settings( )->set_striped_pattern( abap_true ).
        o_salv->get_selections( )->set_selection_mode( if_salv_c_selection_mode=>row_column ).

        SET HANDLER lcl_events=>on_double_click FOR o_salv->get_event( ).

        o_salv->display( ).
      CATCH cx_root INTO DATA(e_txt).
        WRITE: / e_txt->get_text( ).
    ENDTRY.
  ENDIF.

[ABAP] OpenSQL: Coalesce – Fehlende SQL-Werte (NULL) ersetzen 

* http://www.cadaxo.com/sql-cockpit/neue-open-sql-features-ab-abap-7-40-sp8/

* DEMO_SQL_EXPR_COALESCE

* Null-Werte (Nicht vorhandene Email-Adressen), die sich aus dem LEFT OUTER JOIN ergeben, werden durch den String '<leer>' ersetzt
CONSTANTS: co_no_email TYPE ad_smtpadr VALUE '<leer>'.

SELECT u~bname,
       u~persnumber,
       u~addrnumber,
       COALESCE( a~smtp_addr, @co_no_email ) AS email
  INTO TABLE @DATA(it_usr)
  FROM usr21 AS u
  LEFT OUTER JOIN adr6 AS a ON a~persnumber = u~persnumber AND a~addrnumber = u~addrnumber.

IF sy-subrc = 0.
  cl_demo_output=>display( it_usr ).
ENDIF.

[ABAP] ADBC (ABAP Database Connectivity) – Klassen für objektorienterten, dynamischen DB-Zugriff

Variante 1 (Typdefinition)

* http://zevolving.com/2013/05/abap-database-connectivity-adbc/

* Paket
    - SDB_ADBC
* Demoprogramme
    - ADBC_DEMO
    - ADBC_DEMO_LOBS_ORA
    - ADBC_DEMO_METADATA
    - ADBC_DEMO_PROC_CALLS_HDB
    - ADBC_QUERY
    - ADBC_TEST_CONNECTION

TYPES: BEGIN OF ty_s_result_line,
         col1  TYPE c LENGTH 16,            " VARCHAR2 (16 Char)
         col2  TYPE c LENGTH 16,            " VARCHAR2 (16 Char)
         col3  TYPE p LENGTH 10 DECIMALS 0, " NUMBER(10)
       END OF ty_s_result_line .

TYPES: ty_it_result_data TYPE STANDARD TABLE OF ty_s_result_line WITH DEFAULT KEY.

PARAMETERS: p_con TYPE dbcon-con_name DEFAULT 'MYCON'.
PARAMETERS: p_var1 TYPE string DEFAULT 'ABCDEF'.

START-OF-SELECTION.

  TRY.
* Berechtigungsprüfung
      AUTHORITY-CHECK OBJECT 'S_DBCON' ID 'DBA_DBHOST' FIELD space
                                       ID 'DBA_DBSID'  FIELD p_con
                                       ID 'DBA_DBUSER' FIELD space
                                       ID 'ACTVT'      FIELD '03'.

      IF sy-subrc = 0.
* DB Connection erzeugen
        DATA(o_con) = cl_sql_connection=>get_connection( p_con ).
* SQL-Statement (Syntax ist abh. vom Zielsystem, hier ORACLE)
        DATA(lv_stmt) = |SELECT tab1.col1,| &&
                        |       tab2.col2,| &&
                        |       tab3.col3,| &&
                        |  FROM table1 tab1| &&
                        |  INNER JOIN table2 tab2 ON tab1.id = tab2.id| &&
                        |  INNER JOIN table3 tab3 ON tab1.id = tab3.id| &&
                        |  WHERE col4 = ?|.

* Statement erzeugen
        DATA(o_stmt) = o_con->create_statement( ).
* ggf. Parameter (?) setzen
        o_stmt->set_param( REF #( p_var1 ) ).

* SQL-Statement ausführen (mit Rückgabemenge)
        DATA(o_result) = o_stmt->execute_query( lv_stmt ).

* interne Tabelle für Rückgabedaten setzen
        DATA(it_result) = VALUE ty_it_result_data( ).
        o_result->set_param_table( REF #( it_result ) ).

* Daten in interne Tabelle schreiben
* Parameter upto gibt an, wieviele Datensätze gelesen werden sollen, default = 0 (alle)
        DATA(lv_rows) = o_result->next_package( upto = 0 ).

* Sind Daten vorhanden?
        IF lv_rows > 0.
* Datenausgabe
          cl_demo_output=>display( it_result ).
        ELSE.
          WRITE: / 'Keine Rückgabedaten vorhanden.'.
        ENDIF.

* Resultset schließen
        o_result->close( ).
* DB-Connection schließen
        o_con->close( ).
      ELSE.
        WRITE : / 'Keine Berechtigungen.'.
      ENDIF.
    CATCH cx_root INTO DATA(e_txt).
      WRITE: / e_txt->get_text( ).
  ENDTRY.

Variante 2 (generische Typdefinition)

PARAMETERS: p_con TYPE dbcon-con_name DEFAULT 'MYCON'.
PARAMETERS: p_var1 TYPE string DEFAULT 'ABCDEF'.

START-OF-SELECTION.

  TRY.
* Berechtigungsprüfung
      AUTHORITY-CHECK OBJECT 'S_DBCON' ID 'DBA_DBHOST' FIELD space
                                       ID 'DBA_DBSID'  FIELD p_con
                                       ID 'DBA_DBUSER' FIELD space
                                       ID 'ACTVT'      FIELD '03'.

      IF sy-subrc = 0.
* DB Connection erzeugen
        DATA(o_con) = cl_sql_connection=>get_connection( p_con ).
* SQL-Statement (Syntax ist abh. vom Zielsystem, hier ORACLE)
        DATA(lv_stmt) = |SELECT tab1.col1,| &&
                        |       tab2.col2,| &&
                        |       tab3.col3,| &&
                        |  FROM table1 tab1| &&
                        |  INNER JOIN table2 tab2 ON tab1.id = tab2.id| &&
                        |  INNER JOIN table3 tab3 ON tab1.id = tab3.id| &&
                        |  WHERE col4 = ?|.

* Statement erzeugen
        DATA(o_stmt) = o_con->create_statement( ).
* ggf. Parameter setzen
        o_stmt->set_param( REF #( p_var1 ) ).

* SQL-Statement ausführen (mit Rückgabemenge)
        DATA(o_result) = o_stmt->execute_query( lv_stmt ).

* Metadaten
        DATA(o_meta) = o_result->get_metadata( ).
* Strukturdescriptor
        DATA(o_struct) = o_result->get_struct_ref( o_meta ).
        DATA(o_struct_descr) = CAST cl_abap_datadescr( cl_abap_structdescr=>describe_by_data_ref( o_struct ) ).
* Tabellendescriptor erzeugen
        DATA(o_table_descr) = cl_abap_tabledescr=>create( p_line_type = o_struct_descr ).
* Tabellenobjekt anhand des Tabellendeskriptors erstellen
        DATA: o_table TYPE REF TO data.
        CREATE DATA o_table TYPE HANDLE o_table_descr.

* interne Tabelle für Rückgabedaten setzen
        o_result->set_param_table( o_table ).

* Daten in interne Tabelle schreiben
* Parameter upto gibt an, wieviele Datensätze gelesen werden sollen, default = 0 (alle)
        DATA(lv_rows) = o_result->next_package( upto = 0 ).

* Sind Daten vorhanden?
        IF lv_rows > 0.
* Feldsymbol auf das Tabellenobjekt
          FIELD-SYMBOLS <table> TYPE ANY TABLE.
          ASSIGN o_table->* TO <table>.
* Datenausgabe
          cl_demo_output=>display( <table> ).
        ELSE.
          WRITE: / 'Keine Rückgabedaten vorhanden.'.
        ENDIF.

* Resultset schließen
        o_result->close( ).
* DB-Connection schließen
        o_con->close( ).
      ELSE.
        WRITE : / 'Keine Berechtigungen.'.
      ENDIF.
    CATCH cx_root INTO DATA(e_txt).
      WRITE: / e_txt->get_text( ).
  ENDTRY.