Skip to Content

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

·   Scenario#3: Populate a UDF (U_ItemFullName) added to the row of a sales order with the format:  “<ItemCode>-<ItemDescription>”


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]

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”.

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])



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

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]


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


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>”


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’

You must be Logged on to comment or reply to a post.
  • /
  • 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]






    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



    • 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"]

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


      Kind Regards, Yatsea

    • 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

  • 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

      • 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]


        Any thoughts?



        Thank you,


  • 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:



    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

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


    ... 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…

  • 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)


    CASE WHEN t."U_OrdMinQtyReq" > $[$38.11.0] THEN 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,