While creating a view object with the View Object Wizard or editing a view object in the View Object Editor, use the Query page to refine the view object's query on the underlying entity objects. A view object encapsulates SQL code and metadata that maps columns in the SELECT statement to attributes of one or more entity objects.
The Query panel has two modes of operation: normal and expert. Normal mode is for users that do not need to modify the SELECT and FROM portions of the SQL statement. The benefit of this mode is that the mapping to the underlying entity object attributes is taken care of for you. You are still free to modify the Where and Order By conditions of the query.
If you need to modify other parts of the query, such as the SELECT or FROM areas, you will need to select Expert Mode. In this mode, you are responsible for ensuring that the mapping of SQL result columns to entity object attributes is maintained. The framework will default the mapping correctly until you modify the SQL to alter a column in the SQL result set. The following Mapping panel allows you to inspect and possibly alter the entity object attribute mapping.
Warning: If you enter Expert Mode and make changes there, that work may be lost if you choose to switch back out of Expert Mode.
Generated Statement, Query Statement
In normal mode, the Generated Statement field is not editable; it simply shows you the SELECT statement that is being issued based on the list of attributes selected in the Attributes panel. You cannot directly edit the contents of the statement unless you have selected Expert Mode. In normal mode, you can still change the content of the query by using the Where, and Order By fields described below.
In Expert Mode the Query Statement field allows you to edit the SQL query that will be applied to the underlying entity objects. In expert mode you are not restricted to WHERE and ORDER BY statements; you can enter any valid SQL statement.
Query Clauses
Where
Enter a WHERE clause without the WHERE keyword. For example, to apply the constraint where the Department number equals 20, enter:
DEPTNO = 20
Order By
Enter an ORDER BY clause without the ORDER BY keyword. For example, to order the query output by Department number, either, enter DEPTNO directly in the Order By field. Alternatively, you can click Browse to use the Select Attributes dialog box to choose and order attributes that are available for an ORDER BY clause.
Note: The attributes listed in the Order By field will be cleared if you click Browse.
In Expert Mode it is still useful to keep the ORDER BY portion of the clause separate, since the runtime engine might need to append additional WHERE clauses to the query. Keeping the ORDER BY clauses separated will ensure they are applied correctly.
Expert Mode
Click Expert Mode to edit the SQL query directly. Expert mode allows greater control over the entire query statement allowing you to edit it directly. In expert mode you are not restricted to WHERE and ORDER BY statements; you can enter any valid SQL statement.
Note that if you enter expert mode and make changes there, that work may be lost if you choose to switch back out of expert mode.
Use ? style parameters
Parameters can be passed to Views in two styles: ? or :n. Examine the following SQL fragments:
Example 1:
WHERE ? = foo AND ? = bar
Example 2:
WHERE :2 = bar AND :1 = foo AND :1 != :2
Notice in the first example that parameters are taken in order. In the second fragment, parameters can be reused and reordered.
Note: A view link will use the parameter style specified on the destination view object. Once this has been set and the view links created, changing it will require visiting each of the view links via the edit wizards, and fixing the SQL syntax.
Test
Click Test to test whether the syntax of the SQL query is valid. No other checking is performed. If the syntax is valid, JDeveloper returns a message that the code is valid. If the syntax is not valid, JDeveloper returns an error message.