Configuring queries in the Query form

Queries are used to customize views, retrieve search results, and configure lookups. Use the Query form to create and test your queries.

Naming conventions for the query

The naming convention for the query record is <Query Code>\<DBMS ID>.

Some examples are:

  • C_ITEMLOOK1\@
  • ITEMBYSETGET1\@
  • ACTIONSETSYMBLOOK1\@

The <Query Code> must match the value that is in the Query Code column of the Lookup Set > Lookup tab.

This table shows an example of each query type.

Query Type Query Code
Search Results Include VIEW, SEARCHRESULTS, and the object where the results are shown, plus a suffix.

Example: VIEWFORMULASEARCHRESULTS1
Views Include VIEW, the object where the view appears and the name of the viewed object, plus a suffix.

Example: VIEWITEMFORMULA1
Lookups Include LOOK and the object that is being looked for. Example:FORMULALOOK3
This table shows the DBMS ID values.
DBMS ID Description
@ This is the default value; it is used for all database types.
o Oracle database
s SQL Server database
D DBII is not supported.

Creating a query

  1. Open the Query form.
  2. Click Create New Object.
  3. Specify a name for the query.
  4. Specify a description.
  5. Optionally, specify a classification. Class is an informational field. The default value is @.
    • To make this query available for selection in the Search <object> pages, use this naming convention: SEARCH.DIY.<search form name>
    • DIY - as in “Do It Yourself”.
    • When you specify SEARCH.DIY.FORMULA, this query is made available for selection in the SQL Search Query list in the Search Formula page.
  6. Select Visible in API to make the query accessible in the Optiva API.
  7. Specify the SQL code in the Query Text field. Then, click Save.

You can click Run Query to test queries that begin with SELECT statements. See Testing your Select statement in the Query form.