Specifying SELECT statements in the SQL builder

Use a SELECT statement to specify criteria for returning rows from a database table.

The SELECT statement is made up of several clauses including the SELECT clause, FROM clause, WHERE clause, ORDER BY clause, GROUP BY clause and HAVING clause.

Restriction: You cannot create a query by using a view that is derived from a user-defined function.

Prerequisite: Before you specify a SELECT statement, create the statement in the SQL builder.

To specify a SELECT statement in the SQL builder:

  1. Add one or more tables to the statement.
  2. Optional: Create one or more join conditions.
  3. Optional: To eliminate all but one of each set of duplicate rows of the final results table, select the DISTINCT check box in the Design pane.
  4. To specify the columns to use in the statement:
    1. Click the Columns tab in the Design pane.
    2. In the first available cells under the Column heading, select one or more columns to use in the statement, or type one or more expressions, or both. To use a wizard to create a complex expression, click Expression Builder at the end of the list of columns in a cell, and then press Enter.
    3. Optional: Type an alias for the column or expression. You cannot use any spaces in the alias. When you run the SQL statement, the alias appears in the results table instead of the column or expression name.
    4. To show a column or expression in the results table when you run the statement, select its Output check box. To use a column or expression in the statement, but not use it when you run the statement, clear its Output check box. For example, you might want to do this if you are defining an ORDER BY clause on a column and you still want to show all columns in the result set: SELECT * FROM DEPT ORDER BY DEPT.NUMBER.
  5. Optional: To specify the order of the rows in the results table, create an ORDER BY clause for the SELECT statement.
  6. Optional: To specify which rows of the database table to return in the results table, define conditions for the SELECT statement. If you do not specify a condition, all rows of the target database table are returned.
  7. Optional: To define groupings for the statement, create GROUP BY clauses.
  8. Run the statement. The output is shown in the Data Output view.
Related reference
SQL builder for SELECT statements
Columns page

Feedback