Syntax #1: Using Table Name and Field Name
SELECT $[TableName."FieldName"] FROM DUMMY
Scenario#1: FMS query to get the Posting Date in Sales Order screen
SQL Server: SELECT $[ORDR.DocDate]
SAP HANA: SELECT $[ORDR."DocDate"] FROM DUMMY
Key Points:
· HANA SQL/SQL Script is case sensitive. You need to use double quotes (“”) for the exact case when the table name, field name or procedure name contains lower case. Otherwise, without double quotes (“”) HANA SQL Engine in run-time will consider everything as upper case, giving you an error “Invalid column or table name”.
· In our example, the table name is entered without quote marks, since it is upper case, for example, ORDR for the Sales Order entry screen. The field name is mixed case and must be quoted, for example, the DocDate field in ORDR which stores the Posting Date.The formatted search syntax in SQL Server can select a field without a from clause. However, SAP HANA syntax requires "FROM DUMMY”
· The following FMS query (table name with double quotes) in SAP HANA will give an error as “Internal error - 1110”.
SELECT $[“ORDR”."DocDate"] FROM DUMMY
You can refer to the document below for more details about HANA SQL and SQL Script:
SAP HANA Database - SQL Reference Manual
SAP HANA SQLScript Reference
Scenario#2: FMS query to get the Posting Date in Sales Order plus 7 days.
SQL Server: SELECT DATEADD(DD, 7, $[ORDR.DocDate])
SAP HANA: SELECT ADD_DAYS(TO_DATE($[ORDR."DocDate"], 'mm/dd/yyyy'), 7) FROM DUMMY
Key Points:
· · The function name for getting the date is different in SAP HANA. If applicable, please replace the date format 'mm/dd/yyyy’ accordingly.
· · The differences of ADD_DAYS and TO_DATE functions between SQL Server and SAP HANA list below:
HANA format |
MSSQL format |
HANA example |
MSSQL example |
ADD_DAYS (d, n) |
DATEADD(day [/ dd / d], n, datetime) |
ADD_DAYS ('1.1.2012', 4) |
DATEADD(day [/ dd / d], 4, '1.1.2012') |
TO_DATE (expression [, format]) |
CAST (exp AS date), |
TO_DATE ('20120730'), |
CAST('20120730' as date), |
CONVERT(date, exp [, style ]) |
TO_DATE ('20120730', 'yyyymmdd') |
CONVERT(date, '20120730', 112) |
This document Best Practices of SQL in SAP HANA lists best practices of SQL usage on SAP HANA, for User Defined Query, SBO_SP_TransactionNotification and Add-Ons etc in SAP Business One, version for SAP HANA. The best practices involve most frequently used SQL syntax in Server SQL and SAP HANA with samples.
Syntax #2: Using Field Item UID in Screen
SELECT $[$Field_Item_ID.0] FROM DUMMY
The system is able to uniquely identify each field of a document using the field’s index number and field’s column number. If you have activated the system information under View -> System Information, the system displays the field’s item number and the field’s column number in the status bar when you move your mouse over a field in a window.
Scenario#1: FMS query to get the Posting Date.
The Item UID of Posting Date is 10 in the Sales Order window.
SQL Server: SELECT $[$10.0]
SAP HANA: SELECT $[$10.0] FROM DUMMY
Scenario#2: FMS query to get the Posting Date plus 7 days.
SQL Server: SELECT DATEADD(DD, 7, $[10.0])
SAP HANA: SELECT ADD_DAYS(TO_DATE($[10.0], 'mm/dd/yyyy'), 7) FROM DUMMY
Syntax#3: Using Field Item UID and Column UID in matrix
SELECT $[$Field_Item_ID.Field_Column_ID.NUMBER/CURRENCY/DATE/0] FROM DUMMY
You can also use the field’s item number and field’s column number to refer to a field in a matrix on the entry screen. By doing this, the query applies to all document entry screens.
· Use the NUMBER parameter if the field concerned contains an amount and a currency key, and you want to extract the amount only.
· Use the CURRENCY parameter if the field concerned contains an amount and a currency key, and you want to extract only the currency key.
· Use the DATE parameter if the field concerned is a date field and you want to use it for calculations.
· Use 0 to get the value as a string
Scenario#3: Populate a user defined field (U_ItemFullName) added to a document row with the format: “<ItemCode>-<ItemDescription>”
Example:
Item Code: A00001
Item Description: IBM Infoprint 1312
Item Full Name (UDF): A00001-IBM Infoprint 1312
In the Sales Order window:
Fields in Sales Order Window |
Item/Column UID |
Line Details Matrix |
38 |
Item Code |
1 |
Item Description |
3 |
SQL Server: SELECT $[$38.1.0] + '-' + $[$38.3.0]
SAP HANA: SELECT $[$38.1.0] || '-' || $[$38.3.0] FROM DUMMY
In SAP HAHA, symbol || is used the concatenation of string.
e.g., the sql statement "SELECT ‘Hello ’ || 'World’ FROM DUMMY" will return the result as ‘Hello World’
