SAPFree referencePDF, Excel, Word exportSAP Business OneAll SAP Business One tools

SAP Business One Query Parameters Syntax Reference

This reference shows how to add user prompts to SAP Business One saved queries with [%0] parameters, including the DECLARE pattern for variables, date ranges, views and stored procedures. It covers SAP Business One 10.0 on SQL Server and SAP HANA, plus the OUQR and OQCN tables that store queries.

Download:

Pattern	SQL Server syntax	HANA syntax	What the user sees	Notes
Simple parameter	SELECT T0.DocNum, T0.CardName FROM OINV T0 WHERE T0.DocDate >= [%0]	SELECT T0."DocNum", T0."CardName" FROM OINV T0 WHERE T0."DocDate" >= [%0]	A prompt labelled with the field description (Posting Date) and a date picker	B1 replaces [%0] with the value before running the SQL.
Parameter on a key column	WHERE T0.CardCode = [%0]	WHERE T0."CardCode" = [%0]	A prompt with a choose-from-list of business partners	B1 offers the list because it knows the column.
Date range	WHERE T0.DocDate BETWEEN [%0] AND [%1]	WHERE T0."DocDate" BETWEEN [%0] AND [%1]	Two date prompts	Number parameters in the order you want them asked.
Same parameter used twice	WHERE T0.DocDate >= [%0] OR T0.DocDueDate >= [%0]	WHERE T0."DocDate" >= [%0] OR T0."DocDueDate" >= [%0]	One prompt	A repeated number is asked once and reused.
Several parameters, mixed fields	WHERE T0.CardCode = [%0] AND T0.DocDate >= [%1] AND T0.DocStatus = [%2]	WHERE T0."CardCode" = [%0] AND T0."DocDate" >= [%1] AND T0."DocStatus" = [%2]	Three prompts in one window	Each prompt gets the field description as label.
Comment header for variables	/* SELECT FROM [dbo].[OINV] T0 */	Not used	Nothing; it tells B1 which table the parameters refer to	Needed on SQL Server when the parameter is assigned to a variable instead of compared in WHERE.
Variable assigned from a parameter	DECLARE @FromDate AS DATETIME /* WHERE / SET @FromDate = / T0.DocDate */ '[%0]'	Not available in a plain saved query: compare [%0] to the column directly	A Posting Date prompt	The comments /* WHERE / and / T0.DocDate */ give B1 the field for the prompt label and type.
Variable used in the real query	SELECT T0.DocNum, T0.DocTotal FROM OINV T0 WHERE T0.DocDate >= @FromDate	SELECT T0."DocNum", T0."DocTotal" FROM OINV T0 WHERE T0."DocDate" >= [%0]	Results after the prompt	Lets you reuse one value in subqueries, CTEs and UNIONs on SQL Server.
Parameter with a SQL view	/* SELECT FROM [dbo].[OCRD] T0 / DECLARE @Card AS NVARCHAR(50) / WHERE / SET @Card = / T0.CardCode / '[%0]' SELECT FROM dbo.ZV_OPEN_ITEMS V WHERE V.CardCode = @Card	SELECT * FROM "ZV_OPEN_ITEMS" V WHERE V."CardCode" = [%0]	A BP prompt	On SQL Server the comment header points B1 at a real table so the prompt works for a view. ZV_OPEN_ITEMS is an example name.
Stored procedure	/* SELECT FROM [dbo].[OINV] T0 / DECLARE @d AS DATETIME / WHERE / SET @d = / T0.DocDate */ '[%0]' EXEC dbo.Z_SALES_BY_DAY @d	CALL "Z_SALES_BY_DAY" ([%0])	A date prompt, then the procedure result	Procedure names are examples. The procedure must return one result set.
Optional parameter	WHERE (T0.SlpCode = @Slp OR @Slp = -1) with the DECLARE pattern	Not supported the same way; use separate saved queries	A prompt the user may fill with -1 for all	B1 prompts are mandatory by default; use a sentinel value.
FMS and alerts	$[$38.1.0] or $[OINV.CardCode] in formatted searches	Same $[...] syntax	No prompt; the value is taken from the open form	Query Manager uses [%0]; formatted searches use $[...]. Do not mix them.
Saved query table	SELECT IntrnalKey, QCategory, QName, QString FROM OUQR	SELECT "IntrnalKey", "QCategory", "QName", "QString" FROM OUQR	List of all saved queries with their SQL	QString holds the full SQL text. Use it to search queries that use a table or column.
Query category table	SELECT CategoryId, CatName FROM OQCN	SELECT "CategoryId", "CatName" FROM OQCN	Query Manager folders	Join OUQR.QCategory = OQCN.CategoryId.
Find queries by table	SELECT T0.QName, T1.CatName FROM OUQR T0 JOIN OQCN T1 ON T1.CategoryId = T0.QCategory WHERE T0.QString LIKE '%OITW%'	SELECT T0."QName", T1."CatName" FROM OUQR T0 JOIN OQCN T1 ON T1."CategoryId" = T0."QCategory" WHERE T0."QString" LIKE '%OITW%'	Every saved query that reads OITW	Useful before an upgrade or a table change.
Authorization by category	Not SQL: categories are assigned to authorization groups	Same	Users see only the categories their group allows	Set the group per category in Query Manager (Manage Categories) and grant the groups in General Authorizations.
Read-only rule	SELECT only	SELECT only	Do not run INSERT, UPDATE or DELETE	Direct writes to B1 tables are not supported by SAP; use the DI API or Service Layer.

Patterns follow SAP Business One 10.0 Query Manager and Query Generator behaviour and common SAP Community usage. The DECLARE pattern is a SQL Server technique; test it on your version. Object names starting with Z_ or ZV_ are examples.

Get a parameter query built

Describe the report and prompts you need and whether you run SQL Server or HANA, and we will send a saved query ready to paste.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How parameters work

In Query Manager and Query Generator, [%0], [%1] and so on are placeholders. When the query runs, B1 looks at the column each placeholder is compared to, shows a prompt labelled with that field's description, offers a choose-from-list for known key columns and a date picker for dates, then substitutes the value into the SQL. The same number used twice is asked once. Because B1 needs a real column to build the prompt, the parameter must sit in a comparison with a table column in the FROM clause, or, on SQL Server, in the comment pattern that names one.

Compare to a real column: T0.DocDate >= [%0].
Number prompts in the order you want them asked.
Formatted searches use $[...] instead of [%0].

The DECLARE pattern on SQL Server

When you need a value in a variable, for a CTE, a UNION, a view or a procedure, write a comment header naming a real table, for example /* SELECT FROM [dbo].[OINV] T0 */, then DECLARE @FromDate AS DATETIME, then /* WHERE */, then SET @FromDate = /* T0.DocDate */ '[%0]'. The comments tell B1 which field the prompt belongs to so it shows Posting Date and a date picker, while SQL Server ignores them. After that, use @FromDate anywhere in the real query. On HANA saved queries this pattern is not available, so compare [%0] directly with the column.

Worked example: invoices for a customer and date range

On SQL Server: SELECT T0.DocNum, T0.DocDate, T0.DocTotal FROM OINV T0 WHERE T0.CardCode = [%0] AND T0.DocDate BETWEEN [%1] AND [%2] ORDER BY T0.DocDate. The user gets a business partner prompt with a choose-from-list and two date pickers. On HANA the same query quotes the columns: T0."CardCode" = [%0] and T0."DocDate" BETWEEN [%1] AND [%2]. Save it in a category and set the category's authorization group so only the sales team sees it.

Where queries are stored

Saved queries are in table OUQR: IntrnalKey is the key, QName the name, QCategory the category and QString the full SQL text. Categories are in OQCN with CategoryId and CatName. Search QString with LIKE to find every query that reads a table before an upgrade or a UDF change. Treat both tables as read-only: create and change queries in Query Manager or through the DI API, not with SQL updates, which SAP does not support.

Frequently Asked Questions

How do I add a parameter to an SAP Business One query?

Put [%0] where the value goes and compare it to a real column, for example WHERE T0.DocDate >= [%0]. When the query runs, B1 shows a prompt labelled with the field name. Use [%1], [%2] for more prompts. The same number used twice is asked once.

Which table stores all saved queries in SAP B1?

OUQR. QName is the query name, QCategory the category and QString the SQL text. Categories are in OQCN (CategoryId, CatName). Join OUQR.QCategory = OQCN.CategoryId to list queries by folder.

How do I use a variable with a SQL view in a B1 query?

On SQL Server use the comment pattern: /* SELECT FROM [dbo].[OCRD] T0 */, DECLARE @Card, /* WHERE */, SET @Card = /* T0.CardCode */ '[%0]', then SELECT from the view with WHERE CardCode = @Card. On HANA compare [%0] directly with the view column.

Why does my parameter not show a choose-from-list?

B1 only offers a list when it can tie the parameter to a known column, such as CardCode or ItemCode in a table in the FROM clause. If the parameter is in a variable without the comment pattern, or compared to an expression, you get a plain text prompt.

If your users want to ask SAP Business One questions in plain English instead of building parameter queries, Netray can run that assistant on your own server.

Talk to an Expert Explore More Free Tools