Wiki

Scale Your Enterprise

User Tools

Site Tools

Trace: • BigCommerce WebService Access • sap_business_one_integration_for_sql_server_databases • Introduction (Bizweaver) • Introduction (WebService Tool) • Introduction (Decisions) • Introduction to Error Handling • Control Types • Save a Bizweaver Workflow • Salesforce.com API • Introduction (Import/Export)
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/04/17 14:48]
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 10: Line 9:
 EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM <datevalue>) EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM <datevalue>)
 </code> </code>
 +
 +  * 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.
 +
 +<code>
 +add_seconds(t0."createDate",(round((t0."createTime"/100),0) * 3600) + ((t0."createTime" - (round((t0."createTime"/100),0)*100)) * 60)) as "CreateDateTime"
 +</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.1555526923.txt.gz · Last modified: 2019/04/17 14:48 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