ABAP Tip - How to use Native SQL

ABAP Native SQL example

In abap programming, there are two type of SQL -  OPEN SQL and Native SQL.

The basic statement for Native SQL is to start with EXEC and end with ENDEXEC like below.

EXEC SQL [PERFORMING <form>].

        <NATIVE SQL statement>[;]

ENDEXEC.

Let's see the very simple source that selects data from SFLIGHT table with Native SQL.

[ex 1]

REPORT  znativesql_01. DATA : gv_carrid  LIKE sflight-carrid,        gv_connid LIKE sflight-connid. EXEC SQL.   SELECT A.CONNID     INTO :GV_CONNID     FROM SFLIGHT A    WHERE A.MANDT = :SY-MANDT      AND A.CARRID = :GV_CARRID . ENDEXEC.

If you want to describe 'SELECT *', you can write program like ex 2.

[ex 2]

REPORT  znativesql_10. DATA : gs_wa LIKE sflight. DATA : gv_carrid LIKE sflight-carrid VALUE 'AA'. EXEC SQL.   SELECT *     INTO :GS_WA     FROM SFLIGHT A    WHERE A.MANDT = :SY-MANDT      AND A.CARRID = :GV_CARRID ENDEXEC. WRITE : gs_wa-carrid, gs_wa-connid.

DISTINCT statement functions same between Open and Native SQL

SELECT DISTINCT carrid

FROM SFLIGHT

In Open SQL, it's impossible to calculate column when selecting data, while Native Sql allow this. [ex 3] can explain how to add value when selecting column.

[ex 3]

REPORT  znativesql_11. DATA : gv_price LIKE sflight-price. DATA : gv_carrid LIKE sflight-carrid VALUE 'AA'. EXEC SQL.   SELECT price+100     INTO :GV_PRICE     FROM SFLIGHT A    WHERE A.MANDT = :SY-MANDT      AND A.CARRID = :GV_CARRID ENDEXEC. WRITE : gv_price.

In [ex3], Price+100 is not a new column but a way to display data.

Arithmetic Expression for native sql are +, -, *, /.

Besides, you can use Numeric function like below tabular.

ROUND

TRUNC

MOD

POWER

SQRT

SIGN

CHR

For example, you can use ROUND function for rounding decimal point.

EXEC SQL.

  SELECT ROUND(price)

    INTO :GV_PRICE

    FROM SFLIGHT A

   WHERE A.MANDT = :SY-MANDT

     AND A.CARRID = :GV_CARRID

ENDEXEC.

NATIVE SQL and Internal Table

It's also possible to handle the internal table in Nativie Sql by using PERFORMING.

[ex 4]

REPORT  znativesql_02. DATA: BEGIN OF gt_itab OCCURS 0,          carrid LIKE sflight-carrid,          connid LIKE sflight-connid,        END   OF gt_itab. DATA : gv_carrid LIKE sflight-carrid value 'AA'. EXEC SQL PERFORMING APPEND_ITAB.   SELECT A.CARRID, A.CONNID     INTO :GT_ITAB-CARRID, :GT_ITAB-CONNID     FROM SFLIGHT A    WHERE A.MANDT = :SY-MANDT      AND A.CARRID = :GV_CARRID ENDEXEC. FORM append_itab.   APPEND gt_itab.   CLEAR  gt_itab. ENDFORM.                    "APPEND_ITAB

as you saw before, you can use work area in INTO statment.

[ex 5]

DATA : GS_WA LIKE GT_ITAB. EXEC SQL PERFORMING APPEND_ITAB.   SELECT A.CARRID, A.CONNID     INTO :GS_WA     FROM SFLIGHT A    WHERE A.MANDT = :SY-MANDT      AND A.CARRID = :GV_CARRID ENDEXEC. FORM append_itab.   APPEND GS_WA TO gt_itab.   CLEAR GS_WA. ENDFORM.                    "APPEND_ITAB

NATIVE SQL and JOIN

[ex 6] explains well how to join table in native sql.

[ex 6]

REPORT  znativesql_04. DATA: BEGIN OF gt_itab OCCURS 0,          carrid LIKE sflight-carrid,          carrname LIKE scarr-carrname,        END   OF gt_itab. DATA : gv_carrid LIKE sflight-carrid VALUE 'AA'. EXEC SQL PERFORMING APPEND_ITAB.   SELECT A.CARRID, B.CARRNAME     INTO :GT_ITAB-CARRID, :GT_ITAB-CARRNAME     FROM SFLIGHT A, SCARR B    WHERE A.MANDT = B.MANDT          AND A.CARRID = B.CARRID          AND A.MANDT = :SY-MANDT          AND A.CARRID = :GV_CARRID ENDEXEC.

Outer join is also allowed to use like below source.

WHERE A.MANDT(+) = B.MANDT          AND A.CARRID(+) = B.CARRID          AND A.MANDT = :SY-MANDT          AND A.CARRID = :GV_CARRID

NATIVE SQL and SUBSTRING

The developer who used oracle database would know the convenient function of SUBSTR statement. Sadly, OPEN SQL doesn't permit to use SUBSTR, but Native Sql guide us to the more efficient world.

Let's writhe the program that select leading 5 character in flight name.

[ex 7]

REPORT  znativesql_05. ... EXEC SQL PERFORMING APPEND_ITAB.   SELECT A.CARRID, SUBSTR(B.CARRNAME, 1, 5)     INTO :GT_ITAB-CARRID, :GT_ITAB-CARRNAME     FROM SFLIGHT A, SCARR B    WHERE A.MANDT(+) = B.MANDT          AND A.CARRID(+) = B.CARRID          AND A.MANDT = :SY-MANDT          AND A.CARRID = :GV_CARRID ENDEXEC.

SUBSTR is also possible to use in where condition.

EXEC SQL PERFORMING APPEND_ITAB.   SELECT A.CARRID, SUBSTR(B.CARRNAME, 1, 5)     INTO :GT_ITAB-CARRID, :GT_ITAB-CARRNAME     FROM SFLIGHT A, SCARR B    WHERE A.MANDT(+) = B.MANDT          AND A.CARRID(+) = B.CARRID          AND A.MANDT = :SY-MANDT          AND SUBSTR(A.CARRID, 1, 1) = 'A' ENDEXEC.

NATIVE SQL and || operator

There is another useful operator in Natvie Sql. Using || operator enable you concatenate columns at once when selecting.

CONCATENATE carrid connid INTO GV_STR SEPARATED BY SPACE.

[ex 8]

REPORT  znativesql_07. DATA : gv_str TYPE char100. DATA : gv_carrid LIKE sflight-carrid VALUE 'AA'. EXEC SQL.   SELECT A.CARRID || ' ' || A.CONNID     INTO :GV_STR     FROM SFLIGHT A    WHERE A.MANDT = :SY-MANDT      AND A.CARRID = :GV_CARRID ENDEXEC.

NATIVE SQL and COMMIT

You can use COMMIT command when changing data.

EXEC SQL.
       COMMIT
  ENDEXEC.      

EXEC SQL.
       ROLLBACK
  ENDEXEC.      

[ex 9]

REPORT  znativesql_08. DATA : gv_str TYPE char100. DATA : gv_carrid LIKE sflight-carrid VALUE 'AA'. EXEC SQL.   UPDATE SFLIGHT      SET PLANETYPE    = 'KOREA'    WHERE CARRID = 'AA'    AND CONNID = '0017' ENDEXEC. EXEC SQL.   ROLLBACK ENDEXEC.