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

Kategorie: SQL

[ABAP] Tabellenzugriff per RFC-Baustein 

DATA: lv_max_rows TYPE soid-accnt VALUE '100'.       " auf 100 Einträge begrenzen, 0 - alle
DATA: lv_separator TYPE char1 VALUE ';'.             " Spaltenseparator
DATA: it_rfc_stxh TYPE STANDARD TABLE OF char512.    " RFC-Datentabelle, Spaltensumme max 512 Zeichen, da Rückgabetyp Zeile -> TAB512
                                                     " das Problem kann man "umgehen", indem man die Spalten einzeln einliest (Performance!)
DATA: it_fields TYPE STANDARD TABLE OF rfc_db_fld.   " Namen der Spalten
DATA: it_options TYPE STANDARD TABLE OF rfc_db_opt.  " 72 Zeichen
DATA: it_stxh TYPE STANDARD TABLE OF stxh.           " umgewandelte Ausgabetabelle mit richtigen DDIC-Datentypen
DATA: it_split_values TYPE STANDARD TABLE OF string. " Splitzeile für Datenkonvertierung

* Spalten
it_fields = VALUE #( ( fieldname = 'TDOBJECT' )
                     ( fieldname = 'TDNAME' )
                     ( fieldname = 'TDID' )
                     ( fieldname = 'TDTITLE' )
                     ( fieldname = 'TDLUSER' ) ).

* WHERE-Bedingung
it_options = VALUE #( ( |TDOBJECT EQ 'TEXT' AND TDID EQ 'ADRS'| ) ).

* Daten per RFC aus Fremdsystem holen, Userlogin wird abgefragt
* Nachteil: es sind nur einfache Tabellenabfragen möglich, keine JOINS
CALL FUNCTION 'RFC_READ_TABLE' DESTINATION 'XA1'
  EXPORTING
    query_table           = 'STXH'
    delimiter             = lv_separator
    rowcount              = lv_max_rows
  TABLES
    options               = it_options
    fields                = it_fields   " Aufruf: Liste der zu lesenden Felder, Rückgabe: Zu jedem Feld Offset, Länge, Typ, Kurztext
    data                  = it_rfc_stxh
  EXCEPTIONS
    table_not_available   = 1
    table_without_data    = 2
    option_not_valid      = 3
    field_not_valid       = 4
    not_authorized        = 5
    data_buffer_exceeded  = 6
    system_failure        = 7
    communication_failure = 8
    OTHERS                = 9.

IF sy-subrc = 0.
  TRY .
* Datenausgabe
      LOOP AT it_rfc_stxh ASSIGNING FIELD-SYMBOL(<rfc_stxh_line>).

* neue Zeile in der Ausgabetabelle erzeugen
        APPEND INITIAL LINE TO it_stxh ASSIGNING FIELD-SYMBOL(<stxh>).
* Elemente der Datenzeile am Separator splitten
        SPLIT <rfc_stxh_line> AT lv_separator INTO TABLE it_split_values.
* Elemente ausgeben
        LOOP AT it_split_values ASSIGNING FIELD-SYMBOL(<value>).
* Spalte + Zelle der Ausgabetabelle anhand des Spaltennamens holen
* es werden nur Werte die Spalten gefüllt, die in it_fields deklariert wurden
          ASSIGN COMPONENT it_fields[ sy-tabix ]-fieldname OF STRUCTURE <stxh> TO FIELD-SYMBOL(<cell>).
          IF <cell> IS ASSIGNED.
* Split-Wert in die Zelle schreiben
            <cell> = condense( val = <value> ).
          ENDIF.

        ENDLOOP.
      ENDLOOP.

* Ausgabe
* Header
      DATA(lv_head) = ||.

      LOOP AT it_fields ASSIGNING FIELD-SYMBOL(<field>).
        DATA(lv_sl) = strlen( <field>-fieldname ).
        DATA(lv_width) = COND i( WHEN lv_sl < <field>-length THEN lv_sl ELSE <field>-length ).
        lv_head = |{ lv_head }{ substring( val = <field>-fieldname off = 0 len = lv_width ) WIDTH = <field>-length + 1 }|.
      ENDLOOP.

      WRITE: / lv_head.

* Daten
      LOOP AT it_stxh ASSIGNING FIELD-SYMBOL(<line>).
        WRITE: / <line>-tdobject, <line>-tdname, <line>-tdid, <line>-tdtitle, <line>-tdluser.
      ENDLOOP.

    CATCH cx_root INTO DATA(e_text).
      WRITE: / e_text->get_text( ).
  ENDTRY.
ELSE.
  WRITE: / 'Fehler:', sy-subrc.
ENDIF.

[ABAP] Wertebereich einer Domäne auslesen

Variante 1 (get_ddic_fixed_values)

CLASS lcl_domvalues DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS: get_by_type
      IMPORTING
                iv_data               TYPE any
      RETURNING VALUE(rt_ddfixvalues) TYPE ddfixvalues.
    CLASS-METHODS: get_by_name
      IMPORTING
                iv_name               TYPE domname
      RETURNING VALUE(rt_ddfixvalues) TYPE ddfixvalues.
ENDCLASS.

CLASS lcl_domvalues IMPLEMENTATION.

  METHOD get_by_type.
    TRY.
* liefert Typebeschreibung für DDIC-Typ
        rt_ddfixvalues = CAST cl_abap_elemdescr( cl_abap_typedescr=>describe_by_data( iv_data ) )->get_ddic_fixed_values( ).
      CATCH cx_root.
    ENDTRY.
  ENDMETHOD.

  METHOD get_by_name.
    TRY.
* liefert Typebeschreibung für DDIC-Typ
        rt_ddfixvalues = CAST cl_abap_elemdescr( cl_abap_typedescr=>describe_by_name( iv_name ) )->get_ddic_fixed_values( ).
      CATCH cx_root.
    ENDTRY.
  ENDMETHOD.

ENDCLASS.

START-OF-SELECTION.

* Domäne über Variablen-Typ
  DATA(lv_cat) = VALUE cccategory( ).
  DATA(it_by_type) = lcl_domvalues=>get_by_type( lv_cat ).

* Domäne über Name
  DATA(it_by_name) = lcl_domvalues=>get_by_name( 'CCCATEGORY' ).

  cl_demo_output=>write_data( it_by_type ).
  cl_demo_output=>write_data( it_by_name ).

  cl_demo_output=>display( ).

Variante 2 (DD_DOMVALUES_GET)

DATA(it_dd07v_tab) = VALUE dd07v_tab( ).
DATA(lv_rc) = VALUE sy-subrc( ).

CALL FUNCTION 'DD_DOMVALUES_GET'
  EXPORTING
    domname        = 'CCCATEGORY'
    text           = abap_true
*   LANGU          = ' '
*   BYPASS_BUFFER  = ' '
  IMPORTING
    rc             = lv_rc
  TABLES
    dd07v_tab      = it_dd07v_tab
  EXCEPTIONS
    wrong_textflag = 1
    OTHERS         = 2.

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

Variante 3 (SQL)

* Name der Domäne
DATA(lv_domain) = 'CCCATEGORY'.
* Anmeldesprache
DATA(lv_lang) = cl_abap_syst=>get_logon_language( ).

SELECT l~domvalue_l, t~ddtext
  INTO TABLE @DATA(it_dom)
  FROM dd07l AS l
  INNER JOIN dd07t AS t ON l~domname = t~domname AND l~valpos = t~valpos AND l~domvalue_l = t~domvalue_l
  WHERE l~domname    = @lv_domain
    AND t~ddlanguage = @lv_lang.

cl_demo_output=>display( it_dom ).

Variante 4 (cl_reca_ddic_doma)

DATA(it_rsdomaval) = VALUE re_t_rsdomaval( ).

* DDIC-Objekt: Domäne
cl_reca_ddic_doma=>get_values( EXPORTING id_name   = 'CCCATEGORY'
                               IMPORTING et_values = it_rsdomaval ).

cl_demo_output=>display( it_rsdomaval ).

Variante 5 (cl_reca_ddic_doma)

DATA(lv_dd01v) = VALUE dd01v( ).
DATA(it_rsdomaval) = VALUE re_t_rsdomaval( ).

* DDIC-Objekt: Domäne
cl_reca_ddic_doma=>get_complete( EXPORTING id_name      = 'CCCATEGORY'
                                 IMPORTING es_header    = lv_dd01v
                                           et_rsdomaval = it_rsdomaval ).

cl_demo_output=>write_data( lv_dd01v ).
cl_demo_output=>write_data( it_rsdomaval ).
cl_demo_output=>display( ).

Links

[ABAP] Rolle eines Mandanten bestimmen 

* aktueller Mandant
DATA(man) = cl_abap_syst=>get_client( ).

* verfügbare Mandanten
SELECT SINGLE *
  INTO @DATA(lv_t000)
  FROM t000
  WHERE mandt = @man.

IF sy-subrc = 0.
  DATA(it_dd07v_tab) = VALUE dd07v_tab( ).
  DATA(lv_rc) = VALUE sy-subrc( ).

* DDextern: Externe Schnittstelle zum Lesen der Domänenfestwerte
  CALL FUNCTION 'DD_DOMVALUES_GET'
    EXPORTING
      domname        = 'CCCATEGORY' " Feld CCCATEGORY: Client Control: Rolle des Mandanten (Productive, Test,...)
      text           = abap_true    " Text holen
    IMPORTING
      rc             = lv_rc
    TABLES
      dd07v_tab      = it_dd07v_tab
    EXCEPTIONS
      wrong_textflag = 1
      OTHERS         = 2.
  IF sy-subrc = 0.
    IF lv_rc = 0.
      IF line_exists( it_dd07v_tab[ domvalue_l = lv_t000-cccategory ] ).
        DATA(lv_dd07v) = it_dd07v_tab[ domvalue_l = lv_t000-cccategory ].

* logisches System, Mandant und Kategorietext ausgeben
        WRITE: / lv_t000-logsys, lv_dd07v-ddtext.
      ENDIF.
    ENDIF.
  ENDIF.
ENDIF.

[ABAP] Mandanten eines Systems auflisten 

DATA: it_t000 TYPE TABLE OF t000.   " Tabelle für Mandanten

PARAMETERS: cd TYPE cccategory DEFAULT 'P'. " Typ: P -> Produktivsystem

START-OF-SELECTION.
  SELECT * FROM t000 INTO TABLE @it_t000 WHERE cccategory = @cd.

  LOOP AT it_t000 INTO DATA(wa_t000).
    WRITE: / wa_t000-mandt, wa_t000-mtext, wa_t000-cccategory.
  ENDLOOP.

[ABAP] ADBC – ABAP Database Connectivity 

* ab V6.10 verfügbar
* http://zevolving.com/2013/05/abap-database-connectivity-adbc/
* https://archive.sap.com/discussions/thread/447887

* DEMO_ADBC_DDL_DML
* DEMO_ADBC_DDL_DML_BINDING
* DEMO_ADBC_DDL_DML_BULK_ACCESS
* DEMO_ADBC_PREPARED_STATEMENT
* DEMO_ADBC_QUERY
* DEMO_ADBC_STORED_PROCEDURE

DATA: o_salv TYPE REF TO cl_salv_table.        " Anzeigeobjekt SALV Grid
DATA: it_cols TYPE adbc_column_tab.            " Selektierte Spalten
DATA: it_tsp01 TYPE STANDARD TABLE OF tsp01.   " Ausgabetabelle
DATA: par TYPE i VALUE 2000.                   " Parameter für Selektion

START-OF-SELECTION.

* zu verarbeitende Spalten
  it_cols = VALUE #( ( 'RQIDENT' )
                     ( 'RQDEST' )
                     ( 'RQPAPER' )
                     ( 'RQO1NAME' ) ).

* Spaltennnamen durch Komma trennen
  DATA(cols) = to_lower( concat_lines_of( table = it_cols sep = ',' ) ).
* Spaltennamen und Parameter in SQL einfügen
  DATA(lv_query) = |SELECT { cols } FROM tsp01 WHERE rqident > '{ par }'|.

  TRY.
* SQL-Connection öffnen
      DATA(o_sql_connection) = NEW cl_sql_connection( ).

      IF abap_true <> o_sql_connection->is_closed( ).
* SQL-Statement erzeugen
        DATA(o_sql) = NEW cl_sql_statement( con_ref = o_sql_connection ).
* Query ausführen
        DATA(o_result) = o_sql->execute_query( lv_query ).
* Ergebnismenge soll in interne Tabelle it_tsp01 und nur die Spalten, welche in it_cols stehen
        o_result->set_param_table( itab_ref             = REF #( it_tsp01 )
                                   corresponding_fields = it_cols ).
* Ergebnismenge in interne Tabelle lesen
* cnt enthält die Anzahl der gelesenen Datensätze
        DATA(cnt) = o_result->next_package( ).
* Ergebnismenge schließen
        o_result->close( ).
* SQL-Connection schließen
        o_sql_connection->close( ).

        IF cnt > 0.
          cl_salv_table=>factory( IMPORTING
                                    r_salv_table = o_salv
                                  CHANGING
                                    t_table = it_tsp01 ).

          o_salv->get_display_settings( )->set_list_header( |DBMS: { o_sql_connection->get_dbms( ) }| ).
          o_salv->get_functions( )->set_all( abap_true ).
          o_salv->get_display_settings( )->set_striped_pattern( abap_true ).
          o_salv->display( ).
        ENDIF.
      ENDIF.
    CATCH cx_root INTO DATA(e_text).
      WRITE: / e_text->get_text( ).
  ENDTRY.

[ABAP] SMTP-Email-Adresse von Benutzer (User) lesen

Variante 1 (Select)

PARAMETERS: p_uname TYPE usr21-bname.

SELECT u~bname,
       u~persnumber,
       u~addrnumber,
       a~smtp_addr
  FROM usr21 AS u
  INNER JOIN adr6 AS a  ON ( a~persnumber = u~persnumber AND a~addrnumber = u~addrnumber )
  INTO TABLE @DATA(it_userdata)
  WHERE u~bname = @p_uname.

LOOP AT it_userdata ASSIGNING FIELD-SYMBOL(<u>).
  WRITE:/ <u>-bname, <u>-smtp_addr.
ENDLOOP.

Variante 2 (Funktionsbaustein)

DATA: rc    TYPE                   sy-subrc,
      email TYPE                   string,
      err   TYPE STANDARD TABLE OF rpbenerr.

CALL FUNCTION 'HR_FBN_GET_USER_EMAIL_ADDRESS'
  EXPORTING
    user_id       = sy-uname
    reaction      = sy-msgty
  IMPORTING
    subrc         = rc
    email_address = email
  TABLES
    error_table   = err.

WRITE: / sy-uname, email.