Frequently used SQLScript Functions in AMDPs

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