Skip to Content

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

To report this post you need to login first.

15 Comments

You must be Logged on to comment or reply to a post.

  1. Janice Middleton

    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

    (0) 
    1. Yatsea Li 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”]


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

       

      Kind Regards, Yatsea

      (0) 
    1. Yatsea Li 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

      (0) 
    1. Yatsea Li 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 as Best 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

      (0) 
  2. Vitor Vieira

    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

    (0) 
      1. 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,

        Vitor

        (0) 
  3. Chester van Ree

    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

    (0) 

Leave a Reply