Skip to Content
Author's profile photo Sean Holland

Frequently used SQLScript Functions in AMDPs

Based on SAP HANA SPS 10


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.



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.


Select posting_date, day_number, sum(day_number)
From (  Select map(dayname(posting_date),'MONDAY',1,
   ) as day_number,
  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,
From   ( Select i.objectid As quote_guid,
             Substring(i.tabkey,4,32) As line_guid,
              h.udate As del_date,
       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,
                  When Ifnull(item_lvl2.ordered_prod, 'X') = Ifnull(item_lvl2.ordered_prod, 'Y') Then
                End As LVL2_ITEM_EXISTS,
                  When Ifnull(item_lvl3.ordered_prod, 'X') = Ifnull(item_lvl3.ordered_prod, 'Y') Then
                  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),':','')



  • 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,
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,
       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'


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):

Online, Split out by difference Function Categories: SQL Reference – SAP HANA SQL and System Views Reference – SAP Library

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Sean Holland
      Sean Holland
      Blog Post Author

      hi Rahul, apologies I missed this question.

      You may have figured this out already, but you can declare CLOB columns like below.

      Author's profile photo Eldar Faradzhev
      Eldar Faradzhev

      Hey! Ive got a question related to AMDP extraction.


      I need to select data from a HANA model and pass it to BW extrator. I have an Input parametr that i have to populate in order to narrow the result set since the amount of data is huge.


      The question is: How can i pass multiple values to Input parametr (placeholder) in the select statement inside the AMDP method?

      is this correct statement or it should be adjasted somehow?


      Author's profile photo Sean Holland
      Sean Holland
      Blog Post Author


      hi Eldar, not sure if this is related to what is presented in the blog. but here's an example of a select with 3 input parameters on the Calc view.



      Author's profile photo Dan Dan
      Dan Dan

      Dear Sean Holland !

      I have developed an AMDP method and came up with a strange issue that make me confuse about AMDP behavior

      • ¬†When I set a break point in AMDP method, and run the program, it takes only 12 second to complete
      • When I run directly ( remove all break point) it takes more than 7 minuets to complete