HANA SQL Tips

This page offers some tips for users that have experience with T-SQL (Microsoft SQL Server) and are switching to SAP HANA SQL.

• HANA database objects (tables, columns, etc.) are enclosed in double-quotes. Tables do not need to be but it is easier to do it for consistency.
• When dealing with null values, the HANA expression is IFNULL, not ISNULL. Otherwise the syntax is the same.
• HANA uses the function CURRENT_DATE instead of getdate().
• HANA uses the function EXTRACT instead of DATEPART.

EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM <datevalue>)

• In many places, SAP Business One (and other applications) store the date and time in two separate columns. At the same time, it may be necessary to have the two values combined into a single DateTime value. An example of this is for use in a Versago calendar report. The following SQL statement (SQL Server only) is used to create the single DateTime value.

add_seconds(t0."createDate",(round((t0."createTime"/100),0) * 3600) + ((t0."createTime" - (round((t0."createTime"/100),0)*100)) * 60)) as "CreateDateTime"

HANA database objects are case-sensitive. For example, “CardCode” is valid while “cardcode” would not be recognized.

The same issue is found when using User Applied Filters in reports. If the value in the database is “Acme” but the user enters “acme”, a match will not be found.

One way to address this is to add a “Search” field that is all lower (or upper) case. The sample below converts the column “CardName” to all lower case.

SELECT
t0."CardCode", t0."CardName", t0."CardType", t0."UpdateDate", LOWER(t0."CardName") AS "SearchName"
FROM "OCRD" t0

“SearchName” is used for the User Applied Filter but is not displayed in the report presentation. The value in the column “CardName” is displayed in the presentation since it has the proper case. This approach should work anywhere that alpha characters are used in search (or lookup) and you don't want the user to worry about the case. Keep in mind, however, that this only works if the search column 9where the user enters a value) is all lower or all upper case.