5.3.6 Specifying Query Conditions

As you select columns within an object, you can specify conditions on the Conditions tab.

Conditions enable you to filter and identify the data you want to work with. You can use these attributes to modify the column alias, apply column conditions, sort columns, or apply functions.

To specify query conditions:

  1. On the Workspace home page, click SQL Workshop and then Query Builder.

    Query Builder appears.

  2. Select an object from the Object Selection pane.

    The selected object appears in the Design Pane.

  3. Select the columns to be included in your query by clicking the box to the left of the column name.

    When you select a column, you are indicating you want to include it in your query. As you select each column, it appears as a separate row in the Conditions view. Use the Up and Down Arrows to control the order that the columns to be displayed in the resulting query. Available tabs include:

    • Column - Displays the column name.
    • Alias - Specify an optional column alias. An alias is an alternative column name. Aliases make a column name more descriptive, shorten the column name, or prevent possible ambiguous references.
    • Object - Specifies table or view name.
    • Condition - Specify a condition for the column. The condition you enter modifies the query's WHERE clause. When specifying a column condition, you must include the appropriate operator and operand. Consider the following example:
      >=10
='VA'
IN (SELECT dept_no FROM dept)
BETWEEN SYSDATE AND SYSDATE + 15
    • Sort - Type Select a sort type. Options include: Ascending (Asc) and Descending (Desc).
    • Sort Order - Enter a number (1, 2, 3, and so on) to specify the order in which selected columns should display.
    • Show - Select this check box to include the column in your query results. It is not necessary to select Show to add a column to the query for filtering only. For example, suppose you want to create following query:

      SELECT ename FROM emp WHERE deptno = 10

      To create this query in Query Builder:

      1. From the Object list, select emp.

      2. In the Design Pane, select ename and deptno.

      3. For the deptno column, in Condition enter =10 and deselect the Show check box.

    • Function - Select an argument function. Available functions are dependent on the column type and include:

      • NUMBER columns - COUNT, COUNT DISTINCT, AVG, MAX, MIN, SUM

      • VARCHAR2, CHAR columns - COUNT, COUNT DISTINCT, INITCAP, LENGTH, LOWER, LTRIM, RTRIM, TRIM, UPPER

      • DATE, TIMESTAMP columns - COUNT, COUNT DISTINCT, TO_CHAR YEAR, TO_CHAR QUARTER, TO_CHAR MONTH, TO_CHAR DAY, TO_CHAR DAY OF YEAR, TO_CHAR WEEK

    • Group By - Specify columns to be used for grouping when an aggregate function is used. Only applicable for columns included in output.
    • Delete - Deselect the column, excluding it from the query.

    As you select columns and define conditions, Query Builder writes the SQL for you.

  4. To view the underlying SQL, click the SQL tab.

See Also:

Viewing Query Results

Parent topic: Using Query Builder