Previous
Previous
 
Next
Next

Specifying Query Conditions

Conditions enable you to filter and identify the data you want to work with. As you select columns within an object, you can specify conditions on the Conditions tab. 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. Table: Conditions Tab describes the attributes available on the Conditions tab.

    Conditions Tab

    Condition Attribute Description

    Up and Down Arrows

    Controls the order that the columns to be displayed in the resulting query. Click the arrow buttons to move columns up and down.

    See Also: "Viewing Query Results"

    Column

    Displays the column name.

    Alias

    Specify an optional column alias. An alias is an alternative column name. Aliases are used to make a column name more descriptive, to shorten the column name, or prevent possible ambiguous references.

    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 examples:

    >=10
    ='VA'
    IN (SELECT dept_no FROM dept)
    BETWEEN SYSDATE AND SYSDATE + 15
     
    

    Sort Type

    Select a sort type. Options include:

    • Ascending (Asc)

    • 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 uncheck the Show check box.

    Function

    Select an argument function. Available functions include:

    • NUMBER columns - COUNT, COUNT DISTINCT, AVG, MAXIMUM,. MINIMUM, SUM

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

    • DATE, TIMESTAMP columns - COUNT, COUNT DISTINCT

    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.