New Age Open SQL ABAP 740
In this article, we would have some time off from HANA. We would respite and check what is accessible in Open SQL. For what reason is it called Open? You got it right!! Open signifies “Open to any Information base”, for example information base autonomous. You don’t have to have HANA information base to exploit the Open SQL articulations which can further develop the manner in which we foster our applications. New Age Open SQL ABAP 740.
Assuming you have been following the past posts on SAP ABAP on HANA, you would realize that Compact discs View is one more procedure to accomplish Code to Information worldview. In the event that a similar usefulness can be accomplished by the two Discs Strategy and Open SQL, which one would it be a good idea for us to take on? Now start our tutorial on New Age Open SQL ABAP 740.
Reply: SAP believes that us should remain Open. Open SQL is the best option. Then comes Discs View and afterward the put away methods (ADBC, ADMP which we will cover in our ensuing articles).
The entire thought of the cutting edge ABAP/SQL/HANA is to push down rationale to the information base. We re-appropriate these strong developments to put and execute the rationale in the data set. Yet, do recollect, SAP additionally needs to be basically as Open as could really be expected. So given a decision between data set explicit and information base free arrangement, consistently picked the last option (data set autonomous).
Enough of teaching, let us come to the top story. New Age SQL for ABAP.
Preceding delivery 740, assuming we had the necessity to add an extra section in the result which didn’t exist in that frame of mind with some custom rationale, then we typically composed something like beneath.
We characterized the Sorts. We circled through the table and added the custom rationale (High Buy or Low Buy) as displayed beneath.
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.
Allow us to perceive how we can accomplish exactly the same thing in another manner. With ABAP 740 or more, we dispose of TYPES, Information Announcement and Circle. 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.
Yields from both the above methods are same. However, the way does matters. Isn’t it?
Assuming you have some disarray in regards to HANA.
Then, let us really look at the strong inbuilt capabilities 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.
Particular Material is 1 and Unmistakable Plant is 2. Total for the Unlimited stock is 2, AVG is 2/2 = 1 and Amount of Impeded stock is 2. This is only an example to exhibit how flexible and strong the SELECT proclamation has become.
Then, in our menu, today is the Numerical Administrators in SELECT. Check the underneath scrap where we can straightforwardly appoint ’10’ (as refund percent) which would be in the interior table. CEIL capability, increase, deduction and so forth can be taken care of during the SELECT assertion. In the event that we were not in 740, we would have required a different circle and pack of code to accomplish this capability. Isn’t ABAP genuine current at this point?
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.
Math is fun with ABAP 740, yet additionally legitimate programming. Go on underneath to taste the new flavor.
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.
Blend’s exacting importance from the word reference is ‘met up and shape one mass or entire’ or ‘join (components) in a mass or entirety’.
As per SAP documentation, the Mix capability in Open SQL returns the worth of the contention arg1 (in the event that this isn’t the invalid worth); in any case, it returns the worth of the contention arg2. A clear should be set after the initial enclosure and before the end bracket. A comma should be put between the contentions
Really take a look at the use underneath. In the event that information for ekko~lifnr is available (implies PO is made for the lessor) then the LIFNR (Merchant Number) from EKKO is printed else, ‘No PO’ strict is refreshed. This capability is very helpful in numerous genuine functional situations.
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 frequently and in what number of activities did you have the prerequisite to print Endlessly establish depiction together like 0101 (Houston Site) or in structures you had the necessity to compose Payee (Payee Name)? We accomplished it by circling and connecting. We didn’t have better choice prior, yet presently we can do it while choosing the information. On account of the SAP Improvement Group.
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.
Each report/transformation/interface requests that we approve the information and we do it by checking its presence in the actually look at table. That has become simpler and better presently like displayed underneath.
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 dependably a fifth era programming language and it has become all the more so. It has become more comprehensible and genuine grammatically as well. . HAVING capability is one more quill 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.
Keep in mind, some of the time we really want to choose all fields of more than one table and give custom names in the result. Wasn’t it tedious to make TYPEs and accomplish our prerequisite?
Test 9 ( Utilization of choice of all segments with renaming of fields. This is helpful in the event that you need to do all field select )
I thought with ABAP 740, I could do the underneath.
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 linguistically right. Amazing!! I was so eager to test it as it would show all sections from both the tables.
Uh oh!! We receive the above message. Too soon to be so cheerful.
Allow us to change a similar code a tad. We want to characterize the Sorts and pronounce the interior table (Inline didn’t 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 additionally included the section name from second table (not caught in the screen print beneath)
These were only the tip of the chunks of ice. We would coincidentally find more elements and shocks as we work on projects in genuine framework. Just to tell you, all the above code pieces are from a customary data set (not HANA) which has EhP 7.4. So don’t confound that we really want HANA data set to exploit present day SQL strategies. We simply need close or more EhP 7.4.
We inquired as to whether Compact discs Perspectives and SQL can accomplish a similar usefulness. Which one would it be a good idea for us to pick?
Master Simon Bain (Chief SearchYourCloud Inc.) said:
I guess the response would be one more inquiry or set of inquiries. In your application do you right now utilize Compact discs? Are your engineers proficient on Discs? On the off chance that yes to both, most likely Discs Perspectives.
In the event that there is an expectation to learn and adapt, go for the more well known SQL and train the improvement group for the following update, as opposed to placing in code that they are either discontent with or have little information on.
Toward the day’s end, I would agree that utilization whichever one turns out best for your undertaking, group and application. The client shouldn’t see any distinction in convenience. Everything no doubt revolves around support and information by the day’s end.
To get such valuable articles straightforwardly to your inbox, if it’s not too much trouble, Buy in. We regard your security and view safeguarding it in a serious way.
Thank you kindly for your time!!
YOU MAY LIKE THIS
ABAP for SAP HANA. ALV Report On SAP HANA – Opportunities And Challenges
ABAP on SAP HANA: ATC – ABAP Test Cockpit Setup & Exemption Process
Power of Parallel Cursor in SAP ABAP
WhatsApp us