From 7.4 new ABAP , Open SQL is enhanced to take full advantage of S4 hana in-memory capability where we can push down the calculation in the database itself and can minimize the transfer between application and database.
2.1. Inline declaration in select query
SELECT matnr,
mtart
FROM mara
INTO TABLE @DATA(lt_mara)
UP TO 100 ROWS.
NOTE : IF we use any variable/structure/internal tables then we have to add ‘@’ sign at the starting even though its already declared and also fields should be separated using ','.
DATA : lv_mtart TYPE mtart VALUE 'HALB'.
SELECT matnr,
mtart
FROM mara
INTO TABLE @DATA(lt_mara)
UP TO 100 ROWS
WHERE mtart = @lv_mtart. “ ‘@’ is added
2.2. Field list can be placed after ‘FROM’ and the ‘INTO’ can be placed at the end after ‘WHERE’
OLD : ‘INTO’ can’t be at last .
TYPES: BEGIN OF ty_mara,
matnr TYPE matnr,
mtart TYPE mtart,
END OF ty_mara.
DATA : lt_mara TYPE STANDARD TABLE OF ty_mara.
SELECT matnr
mtart
FROM mara
INTO TABLE lt_mara
WHERE mtart = 'FERT'.
NEW : Keyword ‘FIELDS’ can be used to specify the the fields to be selected.
SELECT FROM mara
FIELDS matnr,
mtart
WHERE mtart = 'FERT'
INTO @DATA(lt_mara).
All above examples are easy and self-explanatory hence I have not given screenshot of the output.
2.3. Union
As we can have ‘INTO’ at last so now open SQL can support UNION or UNION ALL also .
Example : This is just for illustration purpose , tables can be different but the pre-requisite(same number of columns and of compatible types ) has be taken case to use UNION and UNION ALL.
SELECT FROM mara
FIELDS matnr,
mtart
WHERE mtart = 'FERT'
UNION "Union or Union ALL both can be used
SELECT FROM mara
FIELDS matnr,
mtart
WHERE mtart = 'HALB'
ORDER BY matnr ASCENDING "Sorting can be done in select query itself
INTO TABLE @DATA(lt_mara).
cl_demo_output=>display_data( EXPORTING value = lt_mara ) .
2.4. Calculation in select query
Fetching data from MARA table to perform in which we will perform the various calculations.
SELECT FROM mara
FIELDS matnr,
mtart,
ntgew
WHERE ntgew <> @abap_false
INTO table @DATA(lt_mara1) .
cl_demo_output=>display_data( EXPORTING value = lt_mara1 ) .
Now We will perform various calculation on the above output values.
DATA : lv_1 TYPE p DECIMALS 1 VALUE '1.2'.
SELECT FROM mara
FIELDS mtart,
ntgew,
( @lv_1 * ntgew ) AS comp_value, " Simple calculation can be done with a variable or a constant also
5 * ntgew AS five_multiple, " using constant
ceil( @lv_1 * ntgew ) AS ceil_value, " Ceil and floor can be used
floor( @lv_1 * ntgew ) AS floor_value,
100 + @lv_1 AS add_value, " Simple addition and subtraction
100 - ( @lv_1 * 10 ) AS calc_col,
'Prefix' && mtart && 'Suffix' AS concat_col, " Concatenation
"mathematical function like sum, avg , min , max can be used
SUM( ntgew ) AS quant_sum,
AVG( ntgew ) AS avg_sum,
max( ntgew ) AS max_sum,
min( ntgew ) AS min_sum,
COUNT( DISTINCT ( matnr ) ) AS unique_mat_type, " count unique material number
CASE WHEN @lv_1 > 2
THEN 'False'
ELSE 'True'
END AS simple_case, " Simple case statement
CASE mtart
WHEN 'HAWA'
THEN 'Trading Good'
WHEN 'FERT'
THEN 'Finished Product'
END AS mat_type_desc " multiple comparison case statement
GROUP BY mtart , ntgew
HAVING ntgew <> @abap_false " 'HAVING' can be used
INTO TABLE @DATA(lt_mara).
cl_demo_output=>display_data( EXPORTING value = lt_mara ) .
Output :
2.5. COALESCE( arg1 , arg2 )
COALESCE : The coalesce function returns the value of the argument arg1
(if this is not the null value); otherwise it returns the value of the argument arg2.
Also in joins we can
now use non-equi joins means we can have now other comparison in addition to equal
sign as NE,GE,GT, LE etc.
SELECT mara~matnr
cl_demo_output=>display_data( EXPORTING value = lt_mard_mara ) . It will display 'Nil' whichever material doesn't have any plant
Output :
SELECT single
@abap_true
FROM mara
INTO @DATA(lv_present)
WHERE MTART = 'TEST'.
If lv_present EQ ‘X’.
SELECT 'I' as sign ,
'EQ' as option,
matnr as low
FROM mara
INTO TABLE @DATA(lt_range) UP TO 4 ROWS.
cl_demo_output=>display_data( EXPORTING value = lt_range ) .
No comments:
Post a Comment