New Age Open SQL ABAP 740
We would take a break from HANA. We would pause and check what is available in Open SQL. Why is it called Open? You guessed it correct!! Open means “Open to any Database“, i.e. database independent. You do not need to have HANA database to take advantage of the Open SQL statements which can improve the way we develop our applications.
If you have been following the previous posts on SAP ABAP on HANA, you would know that CDS View is another technique to achieve Code to Data paradigm. If the same functionality can be achieved by both CDS Technique and Open SQL, which one should we adopt?
Answer: SAP wants us to stay Open. Open SQL is the first choice. Then comes CDS View and then the stored procedures (ADBC, ADMP which we will cover in our subsequent articles).
The whole idea of the modern ABAP/SQL/HANA is to push down logic to the database. We outsource these powerful innovations to put and execute the logic in the database. But do remember, SAP also wants to be as Open as possible. So given a choice between database specific and database independent solution, always chose the latter (database independent).
Enough of preaching, let us come to the topic of the day. New Age SQL for ABAP.
Prior to release 740, if we had the requirement to add an additional column in the output which did not exist in SAP table with some custom logic, then we usually wrote something like below.
We defined the TYPES. We looped through the table and added the custom logic (High Purchase or Low Purchase) as shown below.
TYPES: BEGIN OF ty_ekpo,
ebeln TYPE ebeln,
ebelp TYPE ebelp,
werks TYPE ewerk,
netpr TYPE bprei,
pur_type TYPE char14,
END OF ty_ekpo.
DATA: it_ekpo TYPE STANDARD TABLE OF ty_ekpo.
FIELD-SYMBOLS <fs_ekpo> TYPE ty_ekpo.
SELECT ebeln ebelp werks netpr
FROM ekpo
INTO TABLE it_ekpo.
LOOP AT it_ekpo ASSIGNING <fs_ekpo>.
IF <fs_ekpo>-netpr GT 299.
<fs_ekpo>-pur_type = 'High Purchase'.
ELSE.
<fs_ekpo>-pur_type = 'Low Purchase'.
ENDIF.
ENDLOOP.
IF it_ekpo IS NOT INITIAL.
cl_demo_output=>display_data(
EXPORTING
value = it_ekpo
name = 'Old AGE SQL : 1' ).
ENDIF.
Let us see how we can achieve the same thing in a new way. With ABAP 740 and above, we get rid of TYPES, Data Declaration and Loop. Isn’t it cool?
Sample 1 ( Using comma separated fields with inline data declaration and usage of CASE for reference fields)
SELECT ebeln, ebelp, werks, netpr,
CASE
WHEN netpr > 299
THEN 'High Purchase'
ELSE 'Low Purchase'
END AS pur_type
FROM ekpo
INTO TABLE @DATA(lt_sales_order_header).
IF sy-subrc = 0.
cl_demo_output=>display_data(
EXPORTING
value = lt_sales_order_header
name = 'New AGE SQL : 1' ).
ENDIF.
Outputs from both the above techniques are same. But the path does matters. Isn’t it?
Next, let us check the powerful inbuilt functions in SELECT.
Sample 2 ( Using JOIN and COUNT / DISTINCT functions in SELECT )
PARAMETERS: p_matnr TYPE matnr,
p_lgort TYPE lgort_d.
SELECT mara~matnr,
mard~lgort,
COUNT( DISTINCT ( mard~matnr ) ) AS distinct_mat, " Unique Number of Material
COUNT( DISTINCT ( mard~werks ) ) AS distinct_plant, " Unique Number of Plant
SUM( mard~labst ) AS sum_unrest,
AVG( mard~insme ) AS avg_qlt_insp,
SUM( mard~vmspe ) AS sum_blocked
FROM mara AS mara INNER JOIN mard AS mard
ON mara~matnr EQ mard~matnr
INTO TABLE @DATA(lt_storage_loc_mat)
UP TO 1000 ROWS
WHERE mard~matnr = @p_matnr
AND mard~lgort = @p_lgort
GROUP BY mara~matnr,
mard~lgort.
IF sy-subrc = 0.
cl_demo_output=>display_data(
EXPORTING
value = lt_storage_loc_mat
name = 'New AGE SQL : 2' ).
ENDIF.DISTINCT Material is 1 and DISTINCT Plant is 2. SUM for the Unrestricted stock is 2, AVG is 2/2 = 1 and SUM of Blocked stock is 2. This is just a sample to showcase how versatile and powerful the SELECT statement has become.
Next, in our menu, today is the Mathematical Operators in SELECT. Check the below snippet where we can directly assign ’10’ (as rebate percent) which would be in the internal table. CEIL function, multiplication, subtraction etc can be handled during the SELECT statement. If we were not in 740, we would have needed a separate loop and bunch of code to achieve this function. Isn’t ABAP real modern now?
Sample 3 ( Using vivid mathematical operators in SELECT )
DATA: lv_rebate TYPE p DECIMALS 2 VALUE '0.10'.
SELECT ebeln,
10 AS rebate_per,
CEIL( netpr ) AS whole_ord_net,
( @lv_rebate * netpr ) AS rebate,
( netpr - ( @lv_rebate * netpr ) ) AS act_net
FROM ekpo
USING CLIENT '130'
UP TO 10 ROWS
INTO TABLE @DATA(lt_po_data).
IF sy-subrc = 0.
cl_demo_output=>display_data(
EXPORTING
value = lt_po_data
name = 'New AGE SQL : 3' ).
ENDIF.
Not only Mathematics is fun with ABAP 740, but also logical programming. Continue below to taste the new flavour.
Sample 4 ( Using Complex Case statement on non-referenced fields i.e. multiple in one Select )
PARAMETERS: p_werks TYPE werks_d.
DATA:
lv_rebate TYPE p DECIMALS 2 VALUE '0.10',
lv_high_rebate TYPE p DECIMALS 2 VALUE '0.30'.
SELECT ebeln,
werks,
CEIL( netpr ) AS whole_ord_net,
( @lv_rebate * netpr ) AS rebate,
( netpr - ( @lv_rebate * netpr ) ) AS act_net,
CASE WHEN werks = @p_werks " For specific plant
THEN @lv_rebate
ELSE @lv_high_rebate
END AS rebate_type,
CASE WHEN werks = @p_werks " For specific plant
THEN 'low rebate'
ELSE 'high rebate'
END AS low_high
FROM ekpo
USING CLIENT '130'
UP TO 25 ROWS
INTO TABLE @DATA(lt_po_data).
IF sy-subrc = 0.
cl_demo_output=>display_data(
EXPORTING
value = lt_po_data
name = 'New AGE SQL : 4' ).
ENDIF.
COALESCE’s literal meaning from the dictionary is ‘come together and form one mass or whole‘ or ‘combine (elements) in a mass or whole‘.
According to SAP documentation, the COALESCE function in Open SQL returns the value of the argument arg1 (if this is not the null value); otherwise, it returns the value of the argument arg2. A blank must be placed after the opening parenthesis and before the closing parenthesis. A comma must be placed between the arguments
Check the usage below. If data for ekko~lifnr is present (means PO is created for the lessor) then the LIFNR (Vendor Number) from EKKO is printed else, ‘No PO’ literal is updated. This function is quite handy in many real practical scenarios.
Sample 5 ( Using COALESCE and Logical operators like GE / GT/ LE / LT etc in JOIN which was originally not available
SELECT lfa1~lifnr,
lfa1~name1,
ekko~ebeln,
ekko~bukrs,
COALESCE( ekko~lifnr, 'No PO' ) AS vendor
FROM lfa1 AS lfa1 LEFT OUTER JOIN ekko AS ekko
ON lfa1~lifnr EQ ekko~lifnr
AND ekko~bukrs LT '0208'
INTO TABLE @DATA(lt_vend_po)
UP TO 100 ROWS.
IF sy-subrc = 0.
cl_demo_output=>display_data(
EXPORTING
value = lt_vend_po
name = 'New AGE SQL : 5' ).
ENDIF.
How many times and in how many projects did you have the requirement to print Plant and Plant description together like 0101 (Houston Site) or in forms you had the requirement to write Payee (Payee Name)? We achieved it by looping and concatenating. We did not have better option earlier, but now we can do it while selecting the data. Thanks to the SAP Development Team.
Sample 6 (Concatenation while selecting data )
SELECT lifnr
&& '(' && name1 && ')' AS Vendor,
ORT01 as city
FROM lfa1
INTO TABLE @DATA(lt_bp_data)
UP TO 100 ROWS.
IF sy-subrc = 0.
cl_demo_output=>display_data(
EXPORTING
value = lt_bp_data
name = 'New AGE SQL : 6' ).
ENDIF.
Every report/conversion/interface asks us to validate the input data and we do it by checking its existence in the check table. That has become easier and better now like shown below.
Sample 7 ( Check existence of a record )
SELECT SINGLE @abap_true
FROM mara
INTO @DATA(lv_exists)
WHERE MTART = 'IBAU'.
IF lv_exists = abap_true.
WRITE:/ 'Data Exists!! New AGE SQL : 7'.
ENDIF.ABAP was always a fifth generation programming language and it has become more so. It has become more readable and real life syntactically too. HAVING function is another feather to the crown.
Sample 8 ( Use of HAVING functions in SELECT )
SELECT lfa1~lifnr,
lfa1~name1,
ekko~ebeln,
ekko~bukrs
FROM lfa1 AS lfa1 INNER JOIN ekko AS ekko
ON lfa1~lifnr EQ ekko~lifnr
AND ekko~bukrs LT '0208'
INTO TABLE @DATA(lt_vend_po)
GROUP BY lfa1~lifnr, lfa1~name1, ekko~ebeln, ekko~bukrs
HAVING lfa1~lifnr > '0000220000'.
IF sy-subrc = 0.
cl_demo_output=>display_data(
EXPORTING
value = lt_vend_po
name = 'New AGE SQL : 8' ).
ENDIF.
Remember, sometimes we need to select all fields of more than one table and provide custom names in the output. Wasn’t it tiresome to create TYPEs and achieve our requirement?
Sample 9 ( Use of selection of all columns with renaming of fields. This is handy in case you have to do all field select )
I thought with ABAP 740, I could do the below.
SELECT jcds~*,
tj02t~*
FROM jcds INNER JOIN tj02t
ON jcds~stat = tj02t~istat
WHERE tj02t~spras = @sy-langu
INTO TABLE @DATA(lt_status)
UP TO 1000 ROWS.
IF sy-subrc = 0.
cl_demo_output=>display_data(
EXPORTING
value = lt_status
name = 'New AGE SQL : 9' ).
ENDIF.The above code is syntactically correct. Wow!! I was so excited to test it as it would show all columns from both the tables.
OOPs!! We get the above message. Too early to be so happy.
Let us modify the same code a little bit. We need to define the TYPEs and declare the internal table (Inline did not work above).
TYPES BEGIN OF ty_data.
INCLUDE TYPE jcds AS status_change RENAMING WITH SUFFIX _change.
INCLUDE TYPE tj02t AS status_text RENAMING WITH SUFFIX _text.
TYPES END OF ty_data.
DATA: lt_status TYPE STANDARD TABLE OF ty_data.
SELECT jcds~*,
tj02t~*
FROM jcds INNER JOIN tj02t
ON jcds~stat = tj02t~istat
WHERE tj02t~spras = @sy-langu
INTO TABLE @lt_status
UP TO 100 ROWS.
IF sy-subrc = 0.
cl_demo_output=>display_data(
EXPORTING
value = lt_status
name = 'New AGE SQL : 9' ).
ENDIF.Check _CHANGE is added to the field name. _TEXT is also added in the column name from second table (not captured in the screen print below)
These were just the tip of the icebergs. We would stumble upon more features and surprises as we work on projects in real system. Just to let you know, all the above code snippets are from a traditional database (not HANA) which has EhP 7.4. So do not confuse that we need HANA database to take advantage of modern SQL techniques. We just need near and above EhP 7.4.
