Based on SAP HANA SPS 10
Introduction
While you may have a requirement to use any number of SQLScript functions within your AMDP code, there are a specific number highlighted below that may frequently appear inside AMDP queries & coding logic. In this blog we will look at why these functions are particularly useful inside AMDPs, of course these are not exclusive to AMDP use and for sure it’s not an exhaustive list.
Would also welcome any further suggestions that I can append if folks find this blog productive.
The goal for any AMDP implementation should be to remove any additional logic required in the abap stack, i.e. avoid loops, further joins, formating or data conversion. To achieve this, we should take advantage of HANA SQL functions to ensure the that the data being returned from AMDP is complete and needs zero or a light touch while passing through the ABAP stack.
Tip1: Test the queries thoroughly in SQL editor
Yes, you do really need HANA database access to test and debug AMDPs efficiently. Typically this is a departure from old school ABAP development where database access was locked down and not required.
Not only should you test the results of your queries, but put them through explain & visual plan (HANA Studio required) reviews. Having more capabilities to create procedures directly in the database comes with extra responsibilities around the quality of performance of your sql code.
If you’re not sure on volume of rows that may be returned, restrict the query using Select top N in the query to ensure the amount of data being returned doesn’t negatively impact the system.
Tip2: Test SQL Functions using Dummy table
Rather than plug it in straight into a large complex query, evaluate the expressions or functions using Select from dummy table, this helps you confirm that the results are as expected before adding it into your main query.
E.g.
Date Conversion Functions
- TO_DATS(<date_string>), this gives you the date format in the abap date structure format (YYYYMMDD)
-- Get active Territories
Select TL.territory_guid
From crmd_terr_link as tl
Where tl.client = :iv_client
And tl.valid_from <= to_dats(current_date)
And tl.valid_to >= to_dats(current_date)
- || combine date & time fields.
Select to_timestamp(aldate || altime) as ts
From balhdr
- CURRENT_UTCTIMESTAMP, UTCDATE. Time-stamp data types of SAP applications are often stored in the UTC timezone in the database tables, so if you are doing a date/time comparison in an AMDP, you may need to do a conversion to ensure you are comparing fields that are on the same timezone.
Good example of combining multiple functions to form one timestamp value.
-- Get docs created in the last 15mins
select object_id
from crmd_orderadm_h
where created_at > to_dats(current_utcdate)||replace(to_time(add_seconds(current_utctimestamp, -900)),':','')
Compare/Match Expressions
MAP( expression , search , result [, search , result]… [, default] )
expression: The value to compare.
search: The value that is compared against expression.
result: The value returned, if expression is equal to search.
default: Optional. If no matches are found, the MAP function will return default.
If default is omitted, then the MAP function will return null (if no matches are found)
MAP function has the functionality of an IF-THEN-ELSE statement.
E.g.
Select posting_date, day_number, sum(day_number)
From ( Select map(dayname(posting_date),'MONDAY',1,
'TUESDAY',2,
'WEDNESDAY',3,
'THURSDAY',4,
'FRIDAY',5,
'SATURDAY',6,
'7'
) as day_number,
posting_date
From crmd_orderadm_h
Where created_at >= to_dats(add_days(current_date, -7))||replace(to_time(current_timestamp),':','')
)
Group by posting_date, day_number
order by 2
NULL Values
While ABAP applications will not typically write NULL fields in the database, (script a table to see default values), queries with outer joins will generate null values and we need to handle and process these in AMDP code.
E.g. IS NULL, in this case we’re using the combination of Left outer join and IS NULL to find records that are missing from the Doc lines table, i.e. Deleted Lines.
-- Find Deleted Items for the last 7 days
Select quote_guid,
line_guid,
del_date
From ( Select i.objectid As quote_guid,
Substring(i.tabkey,4,32) As line_guid,
h.udate As del_date,
b.guid
From cdpos As i
Inner Join cdhdr As h On i.mandant = h.mandant
And i.objectclas = h.objectclas
And i.objectid = h.objectid
And i.changenr = h.changenr
Left Outer Join crmd_orderadm_i As b On i.mandant = b.client
And Substring(i.tabkey,4,32) = b.guid
Where i.mandant = '300'
And i.objectclas = 'CRM_ORDER'
And i.tabname = 'CRMA_ORDERADM_I'
And i.fname = 'PRODUCT'
And i.chngind = 'E'
And h.udate||h.utime >= to_dats(add_days(current_date, -7))||replace(to_time(current_timestamp),':','')
And h.udate||h.utime <= to_dats(current_date)||replace(to_time(current_timestamp),':','')
Order By del_date Asc
)
Where guid Is Null
IFNULL, again a very useful function when processing NULL values. Note use of CASE statement also below.
Select Top 1000 qt.guid As GUID_QT,
item.guid As GUID_ITEM,
item.ordered_prod As PRODUCT,
Ifnull(item_lvl2.ordered_prod, 'No Level2 Product Found') As LVL2_PRODUCT,
Ifnull(item_lvl3.ordered_prod, 'No Level3 Product Found') As LVL3_PRODUCT,
Case
When Ifnull(item_lvl2.ordered_prod, 'X') = Ifnull(item_lvl2.ordered_prod, 'Y') Then
1
Else
0
End As LVL2_ITEM_EXISTS,
Case
When Ifnull(item_lvl3.ordered_prod, 'X') = Ifnull(item_lvl3.ordered_prod, 'Y') Then
1
Else
0
End As LVL3_ITEM_EXISTS
From crmd_orderadm_h As qt
Inner Join crmd_orderadm_i As item
On qt.client = item.client
And qt.guid = item.header
And item.parent = '00000000000000000000000000000000'
Left Outer Join crmd_orderadm_i As item_lvl2
On qt.client = item_lvl2.client
And qt.guid = item_lvl2.header
And item.guid = item_lvl2.parent
Left Outer Join crmd_orderadm_i As item_lvl3
On qt.client = item_lvl3.client
And qt.guid = item_lvl3.header
And item_lvl2.guid = item_lvl3.parent
Where qt.client = '300'
And item.changed_at >= to_dats(add_days(current_date, -7))||replace(to_time(current_timestamp),':','')
Miscellaneous
- SESSION_CONTEXT values, although we can always pass the SY structure values down as parameters from ABAP, it is beneficial to beware of some of the variables that the controlling abap stack will set for a connection to HANA. These may be enhanced over time with more session variables added.
Select SESSION_CONTEXT('CLIENT') as client,
SESSION_CONTEXT('APPLICATIONUSER') as app_user,
SESSION_CONTEXT('APPLICATION') as appl
From dummy
- CONVERT_CURRENCY, in the case where we have access to the currency conversion tables (TC*), we can do the currency conversion on the fly while selecting the data.
Select object_id,
h.net_value,
Round(Convert_currency(amount => h.net_value,
source_unit_column => prc.currency,
schema => 'SAPSR3', --current_schema,
target_unit_column => 'USD', -- iv_target_currency,
reference_date => prc.price_date,
error_handling => 'set to null',
client => '300') -- :iv_client)
, 2) As usd_currency_val,
prc.currency As qt_currency
From crmd_orderadm_h As qt
inner join crmd_cumulat_h As h
On qt.client = h.client
And qt.guid = h.guid
inner join crmd_link As prc_link
On qt.client = prc_link.client
And qt.guid = prc_link.guid_hi
And prc_link.objtype_set = '09'
inner join crmd_pricing As prc
On qt.client = prc.client
And prc_link.guid_set = prc.guid
Where qt.created_at > '20160620000000'
Conclusion
I hope this blog is a good introduction to the use of SQL Functions within abap managed database procedures, again there’s a lot more functions available, but the idea is to utilise as much as these possible within the HANA database layer, rather than bringing data back to the ABAP stack and doing the manipulation there. I typically used well known CRM standard tables in the examples, but these could be replaced with outer transactional tables with ease, depending on the suite application.
SQLScript Reference Links
Current Documentation (SPS12 as of June ’16): http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf
Online, Split out by difference Function Categories: SQL Reference – SAP HANA SQL and System Views Reference – SAP Library
Hi Sean,
I have a question which is not related to your blog. But, reading your blogs I guess you might know the answer to this.
We are trying to use APL for some analytics use case. We call APL procedures in an AMDP from abap. APL procedure returns a table with a field of type CLOB. We need to store this in ABAP table(which eventually is a HANA table I understand, but we need to define it in ABAP because of transport restrictions).
While defining this table(which will hold the result from APL procedure) in ABAP, we are not able to find an equivalent of CLOB/NCLOB in ABAP types.
Are you aware of any data type in abap which we can use here?