Wiki

Scale Your Enterprise

User Tools

Site Tools

Trace: • Workflow Tools • HANA SQL Tips • Called from another Workflow • Changing A Database Connection Record • Save a Bizweaver Workflow • Calculated Fields • Filters • BigCommerce WebService Access • Setup • Introduction (Looping Tool)
bw:hana_sql_tips

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
bw:hana_sql_tips [2019/10/11 09:12]
runger 		bw:hana_sql_tips [2020/08/10 14:34] (current)
Line 1: Line 1:
-====== HANA SQL Tips ======+======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. 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.   * 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.
-  * HANA database objects are case-sensitive.  For example, "CardCode" is valid while "cardcode" would not be recognized. 
   * When dealing with null values, the HANA expression is **IFNULL**, not ISNULL.  Otherwise the syntax is the same.   * 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 **CURRENT_DATE** instead of getdate().
Line 16: Line 15:
 add_seconds(t0."createDate",(round((t0."createTime"/100),0) * 3600) + ((t0."createTime" - (round((t0."createTime"/100),0)*100)) * 60)) as "CreateDateTime" add_seconds(t0."createDate",(round((t0."createTime"/100),0) * 3600) + ((t0."createTime" - (round((t0."createTime"/100),0)*100)) * 60)) as "CreateDateTime"
 </code> </code>
 +
 +====Handling Case Sensitivity====
 +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.
 +
 +<code SQL>
 +select
 +t0."CardCode", t0."CardName", t0."CardType", t0."UpdateDate", lower(t0."CardName") as "SearchName"
 +from "OCRD" t0
 +</code>
 +
 +"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.
bw/hana_sql_tips.1570799544.txt.gz · Last modified: 2019/10/11 09:12 by runger

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial 4.0 International
CC Attribution-Noncommercial 4.0 International Valid HTML5 Valid CSS Driven by DokuWiki