Query Definition: Filter Prompts 
On the Filter Prompts page, you can set the prompts for your query. For a query on the list page, this provides the default set of filters.
Prompt parameters allow the user to filter the returned results. For example, a user can choose to see all suppliers with names like “Acme”, where the user supplies the value “Acme” at runtime.
If the query includes these parameters, they are defined in two places: initially as a placeholder in the query string and then on the Filter Prompts page.
Adding Parameters
First, the parameter is added to the query string. Using the example above, the query string would appear similar to the following:
SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_MAS_SUPPLIER T1
WHERE T1.INACTIVE = 0 AND T1 CONTEXTID =
<%CONTEXT(masterdata.Supplier)%> AND T1.DISPLAY_NAME LIKE ?
<%ORDERBY%>
The question mark (?) is the placeholder for the prompted parameter. If this standard SQL syntax with the parameter marker is used, a parameter value must be entered and the parameters are positional: the order and type of the prompt field definitions in the query definition must match the order of the corresponding question marks in the query string. This syntax was used for illustration, primarily to make the example simpler to understand. It is recommended that optional parameters be used to make the query more generally useful.
Optional Parameters
You can specify that a query parameter value is optional and exclude the condition in the WHERE clause that is associated with this parameter if the value is not provided. To do this, use the AND attribute.
For example, if DisplayName is the name of the optional parameter, you would provide the following query string with the AND attribute:
SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_MAS_SUPPLIER T1
WHERE T1.INACTIVE = 0 AND AND T1.CONTEXTID = <%CONTEXT(masterdata.Supplier)%>
<%AND(“UPPER(T1.DISPLAY_NAME) LIKE <%?(DisplayName)%>”)%>
<%ORDERBY%>
An even more flexible alternative is to use the AND_OP attribute, which allows the SQL condition operator to be defined by default in the parameter definition and optionally overridden at run time by the user when the query is used in a list page.
SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_MAS_SUPPLIER T1
WHERE T1.INACTIVE = 0 AND AND T1.CONTEXTID = <%CONTEXT(masterdata.Supplier)%>
<%AND_OP(“UPPER(T1.DISPLAY_NAME)”,<%?(T1.DISPLAY_NAME)%>)%>
<%ORDERBY%>
These examples of optional attributes demonstrate the use of the “named parameter” attribute, with the format <%?(paramName)%>. This named parameter attribute is ultimately replaced with a single question mark (a parameter marker) if a filter value is provided. Although this attribute is more complicated than a parameter marker, it enables the parameter definitions in the query to be freed from the constraint of matching the number and order of the corresponding conditions in the WHERE clause.
“paramName” in the named parameter attribute is most understandable when it describes the field being filtered. The conventional format is “UpperCamelCase”, e.g. “DisplayName”. For queries used in list pages, however, a format called “table column name” is advised. This is used in the previous example, “T1.DISPLAY_NAME”. See the section Using the AND_OP Attribute for details.
Defining Parameters
The second step is the definition of each parameter. Select the Filter Prompts tab to display a list of the existing parameter prompts. In edit mode, you use the Add button to define additional prompts. Choosing Add or an existing prompt displays the Query Parameters dialog box.
For field-specific information for this page, see Field Help for Query Definition: Query Parameters.