Skip to Content
Author's profile photo Yatsea Li

Syntax of Formatted Search in SAP Business One, version for SAP HANA

This document will guide you through the syntax of User-defined Values (also known as formatted search)in SAP Business One, version for SAP HANA. The specific syntax of formatted search is still valid in B1H, such as
– $[TableName.FieldName]
– $[$Field_Item_Id.0]
– $[$Field_Item_Id. Column_Id.0] etc.

However, there are some differences from the SQL Server version. This document will point out these syntax differences using the scenarios below:

·Scenario#1: Copy the posting date to the delivery date in a Sales Order using a query

·Scenario#2: Copy the posting date + 7 days to the delivery date in a Sales Order using a query

·场景# 3:填充一个UDF (U_ItemFullName)补充道to the row of a sales order with the format: “-

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,ORDRfor the Sales Order entry screen. The field name is mixed case and must be quoted, for example, theDocDatefield 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:SELECTADD_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 ofADD_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 documentBest Practices of SQL in SAP HANAlists 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 underView ->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:SELECTADD_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 theNUMBERparameter if the field concerned contains an amount and a currency key, and you want to extract the amount only.

·Use theCURRENCYparameter if the field concerned contains an amount and a currency key, and you want to extract only the currency key.

·Use theDATEparameter if the field concerned is a date field and you want to use it for calculations.

·Use0to get the value as a string

Scenario#3: Populate a user defined field (U_ItemFullName) added to a document row with the format: “-

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’

Assigned Tags

      18 Comments
      You must beLogged onto comment or reply to a post.
      Author's profile photo Nagarajan K
      Nagarajan K

      Hi,

      Thanks for explaining the difference between SQL and HANA format in FMS.

      Best Regards,

      Nagarajan

      Author's profile photo Former Member
      Former Member

      Hi,

      Thanks for explanation.

      Regards

      Manoj

      Author's profile photo Former Member
      Former Member

      Hi,

      Good for consultant as well as user of SAP HANA.

      Regards,

      Amit

      Author's profile photo Former Member
      Former Member

      Hi,

      Thanks For Document.

      Author's profile photo Former Member
      Former Member

      How would I convert the following formatted search into the correct syntax for HANA.

      SELECT T0.[DocNum] FROM OWOR T0 where U_SO1= $[ORDR.DocNum] or U_SO2 = $[ORDR.DocNum] or U_SO3 = $[ORDR.DocNum] or U_SO4 = $[ORDR.DocNum] or U_SO5 = $[ORDR.DocNum]

      I TRIED THIS :

      SELECT $[OWOR."DocNum"] FROM DUMMY WHERE "U_SO1" = $[ORDR."DocNum"] or "U_SO2" = $[ORDR."DocNum"] or "U_SO3 "= $[ORDR."DocNum"] or "U_SO4" = $[ORDR."DocNum"] or "U_SO5" = $[ORDR."DocNum"]

      But it does not work.

      Thanks in advance

      Janice

      Author's profile photo Yatsea Li
      Yatsea Li
      Blog Post Author

      Hi Janice,

      Since U_SO1/2/3/4/5 are UDF in OWOR, the query should be:

      SELECT $[OWOR."DocNum"] FROM "OWOR" WHERE "U_SO1" = $[ORDR."DocNum"] or "U_SO2" = $[ORDR."DocNum"] or "U_SO3 "= $[ORDR."DocNum"] or "U_SO4" = $[ORDR."DocNum"] or "U_SO5" = $[ORDR."DocNum"]


      TableDUMMYis only applicable for calling a SQL Function, Field Item etc, which is not related any physical table.

      Kind Regards, Yatsea

      Author's profile photo Humberto Neira
      Humberto Neira

      Hi,

      How can I obtain the connected B1 user using FMS?

      In SQL is like this : select $[$user]

      regards

      Author's profile photo Former Member
      Former Member

      Hi Humberto,

      Your SQL Query: Select $[$User]

      In SAP HANA: Select $[Tablename."User"] from Dummy

      Author's profile photo Yatsea Li
      Yatsea Li
      Blog Post Author

      Hi Humberto,

      If you want the current user code, such as "manager", you may use this:

      SELECT "USER_CODE" from "OUSR" WHERE "USERID" = $[user]

      If you just need the UserID, this is clearly explained in this document.

      SELECT $[user] FROM DUMMY

      Kind Regards, Yatsea

      Author's profile photo Emelia Yamson
      Emelia Yamson

      HI,

      How do we change CAST and CASE queries after i migrate them into HANA?

      thanks

      Author's profile photo Yatsea Li
      Yatsea Li
      Blog Post Author

      Hi Emelia,

      First of all, you're strongly recommended to spend some time on teaching yourself about HANA SQL/SQLScript syntax, which will be very beneficial for the HANA project implementation.

      A handy HANA SQL syntax and SQL Server TSQL syntax comparision is mentioned in the document above asBest Practices of SQL in SAP HANA

      Data Type Conversion:

      HANA has CAST function, TO_DATE, TO_VARCAHR, TO_INTEGER etc. Please check this help.

      SAP HANA SQL and System Views Reference - SAP Library

      Conditional Process:

      CASE...WHEN in HANA SQL

      Expressions - SAP HANA SQL and System Views Reference - SAP Library

      Kind Regards, Yatsea

      Author's profile photo Former Member
      Former Member

      Hi,

      I'm trying to run 'SELECT CURRENT_USER from DUMMY' in a B1 v9.1 PL04 system and it doesn't execute, not even gives an error message... The same goes for 'SELECT $[user] FROM DUMMY'.

      Any tips?

      Thanks in advanced,

      Vitor Vieira

      Author's profile photo Former Member
      Former Member

      hi Vitor

      Are you on SAP B1 HANA?

      if so the query should work. However "CURRENT_USER" will give you your SYSTEM user in the result of query,

      Janice

      Author's profile photo Former Member
      Former Member

      Hi Janice,

      Many thanks for your reply.

      My mistake, I pasted the wrong text.

      The query I'm trying to run and it doesn't is:SELECT "USER_CODE" from "OUSR" WHERE "USERID" = $[user]

      任何想法吗?

      Thank you,

      Vitor

      Author's profile photo Former Member
      Former Member

      Dynamic query in a Formatted Search based on ObjectType.

      Today I ran into the problem that you cannot find the table name without knowing in which object type you are for making a dynamic query.

      The problem is that you cannot retrieve the object type with a FS Variable ($[OINV.ObjType.0]) without knowing the table.

      I’m guessing that many others might have run into this problem so I’m posting my solution to this problem here:

      I’ve noticed that numbering series are always attached to the ObjectType in the NNM1 Table (numbering series table) So the only thing you need to do is find out which Series you have in the document you have open. (This value you can retrieve by using: $[$88.0.0])

      So now that we know which Object type we are when we run a formatted search you can make a dynamic query.

      Build your query into a string, then execute the string.

      Like the below example to retrieve the header info of either an Invoice OINV , Delivery ODLN or Order ORDR:

      DECLARE @SQL NVARCHAR(MAX)

      SET @SQL = ‘select * from O’
      + (Case (SELECT ObjectCode from NNM1 where Series = $[$88.0.0]) when 13 then ‘INV’ When 15 then ‘DLN’ when 17 then ‘RDR’ END)
      + ‘ WHERE DocNum = ‘+ $[$8.0.0]
      EXEC (@SQL)

      Kind regards,

      Chester van Ree

      Author's profile photo Eduardo Márquez Mejía
      Eduardo Márquez Mejía

      As i mentioned in one of your previous post...:

      (//www.bouseh.com/2013/10/15/implementing-sbosptransactionnotification-of-sap-business-one-version-for-sap-hana/)

      ... this is a great contribution for all of the SBO Consultants in the growing World of SBO for HANA… Thank you very much for that…

      作者的简介照片乔丹万博新体育手机客户端Bejar
      Jordan Bejar

      Hi Yatsea,

      When using either of those formatted search in SAP, what is the datatype does the formatted search cast into the query? I'm having issue trying to compare a formatted search where it won't take numeric(19,6) values.

      The error code I get was "General error;339 invalid number: '100.000000' isn't a valid numeric value '' (ECM2)".

      See query below. (This is for Sale Order Module)

      SELECT

      当t。“U_OrdMinQtyReq”>[美元38.11.0]然后t。"U_OrdMinQtyReq" else $[$38.11.0] end as "Qty"

      FROM OITM t WHERE t."ItemCode" = $[$38.1.0]

      The "U_OrdMinQtyReq" is a numeric User-defined field in the Item Master Data. I've tried casting it to_integer(), but it gave me a different error, related to this issue.

      I'm strongly suspecting that the formatted search is inserting the value as string, rather than it's respective datatype from the form.

      Thanks in advance,

      -J

      Author's profile photo Aakash Rajwani
      Aakash Rajwani

      You're comparing it to a string: $[$38.11.0]

      .0 means a string

      Use $[$38.11.NUMBER] instead.