SQL Script for AMDP: Data Declaration & Selects

In this post, you will learn to use SQL Script in AMDP for

1. Data Declaration
2. Select Statements

Data Declaration in SQL Scripts

How to declare internal table in AMDP?

A common requirement in AMDP is to declare and use a runtime table or internal table. Standard program demo_amdp_abap_types_access explains how to declare a table.

Class Definition

Class Implementation

• A data element, generic type or types declared in the class can be used as types for table fields. As these are ABAP data types, $ABAP.type is used while declaring the table.
• Internal table is declared using declare and table keywords.
• When a select query can be written in on database table or internal table, the result is created as internal table automatically.

How to declare variables and constants in AMDP?

Constants and variables can be declared using the keyword declare with the SQL Script types.

SQLScript Types supported in HANA are as below.

• Numeric
  1. TinyINT − 8 bit unsigned integer [0 to 255]
  2. SMALLINT − 16 bit signed integer. [-32,768 to 32,767]
  3. Integer − 32 bit signed integer. [-2,147,483,648 to 2,147,483,648]
  4. BIGINT − 64 bit signed integer. [-9,223,372,036,854,775,808 to 9,223,372,036,854,775,808]
  5. SMALL − Decimal [-10^38 +1 to 10^38 -1 ]
  6. REAL − Decimal [-3.40E38 to 3.40E38]
  7. DOUBLE − 64 bit floating point number. [-1.7976931348623157E308 to 1.7976931348623157E308]
• Character & String
  1. Varchar − maximum of 8000 characters.
  2. Nvarchar − maximum length of 4000 characters
  3. ALPHANUM − Alphanumeric characters
  4. SHORTTEXT − stores variable length character string which supports text search features and string search features.
• Date Time
  1. DATE − Date [YYYY-MM-DD]
  2. TIME − Time [HH: MI: SS]
  3. SECOND DATE − Date and Time [YYYY-MM-DD HH:MM:SS]
  4. TIMESTAMP − Date, Time and Fraction of second [YYYY-MM-DD HH:MM:SS:FFn]
• Large Objects
  1. NCLOB − Large UNICODE character object.
  2. BLOB − Large amount of Binary data.
  3. CLOB − Large amount of ASCII character data.
  4. TEXT − it enables text search features.
• Binary
  1. VARBINARY − binary data in bytes. Max integer length is between 1 and 5000.
• Boolean
  1. Store Boolean value i.e. TRUE/FALSE

CLASS zjp_amdp_sql_1 DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES : if_amdp_marker_hdb,
      if_oo_adt_classrun.

    TYPES:
      BEGIN OF ty_data,
        uname  TYPE usr21-bname,
        langu  TYPE sy-langu,
        datum  TYPE sy-datum,
        text   TYPE c LENGTH 10,
        number TYPE i,
      END OF ty_data,
      tt_data TYPE STANDARD TABLE OF ty_data WITH EMPTY KEY.

    CLASS-METHODS data_declaration
      EXPORTING VALUE(et_data) TYPE tt_data
      RAISING   cx_amdp_error.

ENDCLASS.

CLASS zjp_amdp_sql_1 IMPLEMENTATION.
  METHOD if_oo_adt_classrun~main.
    data_declaration( IMPORTING et_data = DATA(lt_data) ).
    out->write( lt_data ).
  ENDMETHOD.

  METHOD data_declaration BY DATABASE PROCEDURE
                          FOR HDB LANGUAGE SQLSCRIPT.

    declare lc_yes constant nvarchar(3) := 'YES';
    declare lc_no  constant nvarchar(2) := 'NO';

    declare lv_char char(2);
    declare lv_varchar varchar(10);
    declare lv_counter integer;
    declare lv_date date;
    declare lv_time time;

    declare mytab table( uname  "$ABAP.type( usr21-bname )",
                         langu  "$ABAP.type( syst_langu )",
                         datum  date,
                         text   "$ABAP.type( ty_data-text )",
                         number "$ABAP.type( i )" );

    mytab.uname[1]  := 'JAGDISH';
    mytab.langu[1]  := 'E';
    mytab.datum[1]  := session_context('SAP_SYSTEM_DATE');
    mytab.text[1]   := cast( 0123456789 as "$ABAP.type( ty_data-text )" );
    mytab.number[1] := 1;

    et_data = SELECT * FROM :mytab;

  ENDMETHOD.
ENDCLASS.

Select Statements in SQL Scripts

AMDP is one of the code-push-down or code-to-data technique which means selects are integral part of AMDP. Here are examples of few selects.

Before writing any select queries, the tables used in FROM clause of the select query should be mentioned is USING clause of the method implementation.

it_carriers     = select * from scarr;

it_connections  = select * from spfli;                      
it_flights      = select * from sflight;

SELECT INTO TABLE

The where condition can be hard-coded, import parameter can be used with prefix : or session_context variables can be used.

it_carriers     = select * from scarr
                    where carrid = 'AA';
it_connections  = select * from spfli
                    where carrid = :iv_carrid
                    and   connid = :iv_connid;
it_flights      = select * from sflight
                    where carrid = :iv_carrid
                    and   connid = :iv_connid
                    and   fldate = session_context('SAP_SYSTEM_DATE');

SELECT SINGLE

To select one record, TOP can be used. Select single into a work area is not available.

it_carriers = select top 1 * from scarr
                where carrid = 'AA';

Select specific columns

it_carriers = select carrid, carrname from scarr
                where carrid = 'AA';

Select with For all Entries / Selecting data based on internal table

it_connections = select * from spfli
                   where carrid = :iv_carrid
                   and   connid = :iv_connid;

it_flights = select a.carrid, a.connid, a.fldate, 
                    a.planetype, a.paymentsum, a.currency
                    from sflight as a
                    inner join :it_connections as b
                    on    a.carrid = b.carrid
                    and   a.connid = b.connid
                    where a.carrid = :iv_carrid
                    and   a.connid = :iv_connid
                    and   a.fldate = session_context('SAP_SYSTEM_DATE');

Note that for declaring a table based on other table, we have to use the select statement.

lt_flight_2 = :it_flights ; -- This is not allowed

Instead, use a SELECT statement

lt_flight_2 = SELECT * FROM :it_flights ; 

Visit ABAP on HANA series for Tutorials on CDS, AMDP, Eclipse, and ALV IDA.

---

If you like the content, please subscribe…