Creating a Query

You can create a query by filling in the fields on the Query page and adding the list of columns to be selected.

The Query String field is the heart of the query when the execution type is SQL. It is the skeleton of the SQL statement that is to be run. Here is a simple example:

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_MAS_CURRENCY WHERE INACTIVE = 0 AND CONTEXTID=<%CONTEXT(masterdata.Currency)%> <%ORDERBY%>

This query returns the selected column values for all active currencies in the user's context.

Query attributes provide flexibility and advanced capabilities. For complete details, see Query Definition: Attribute Substitution. The four most commonly used attributes are:

<%RESULTS%>

Instead of supplying the list of columns to be selected directly in the Query String, use the <%RESULTS%> attribute to direct the Query component to build the SQL SELECT clause from the information supplied in the Result Fields tab of the Query Definition.

<%SCHEMA%>

This attribute is used to add the Oracle schema identifier in front of each table name in the SQL FROM clause. A period must be inserted after the <%SCHEMA%> attribute and before the table name.

This must be used with all table, view, function, and stored procedure names in all queries.

<%CONTEXT(name)%>

This attribute is used to ensure that the query results are filtered such that only rows in the same context as the currently logged-on user are returned. This attribute should be included in all queries.

For details on determining the (name) portion of the attribute, see Query Definition: Attribute Substitution.

<%ORDERBY%>

This attribute directs the Query component to build the SQL ORDER BY clause using the Sort information in the Result Fields tab of the Query Definition object.

Note that the attributes must be spelled and capitalized exactly as shown, including the delimiter characters. If they do not match exactly, the attributes will not be substituted and the query will fail to run.

Providing Locale-Specific Strings

The display name and description of the query cannot be entered directly, as this does not allow the query to be localized. Instead, string fields in queries (and other objects) that are to be displayed elsewhere in the product (such as on a list page) should be defined as Localized Resources. Each resource is a code that is replaced on the fly with a string that has been translated into the language of the current user. In edit mode, the Display Name Resource and Description Resource field controls are pickers for system resources.

For field-specific information for this page, see Field Help for Query Definition: Query.