Use the SQL Assist SmartGuide to visually compose an SQL statement for a
Select bean.
Pages
Use the Tables page of the SQL Assist SmartGuide to specify the tables that
are accessed in an SQL statement. The table names that are selected
will appear in the FROM clause of the SQL statement.
Fields
- Table name lists the tables that are accessible in the database
identified by the currently selected connection alias. Checking the
checkbox for a table named in the list, selects the table for inclusion in the
FROM clause of the SQL statement. More than one table can be selected
from the list.
Push buttons
- View schema(s) displays a prompt for filtering
characters. These characters limit the display to only table names
whose schema begins with those characters. The % character is a
wildcard character.
- Filter table(s) displays a prompt for filtering
characters. These characters limit the display to only table names
beginning with those characters. The filtering characters are
case-sensitive. The % character is a wildcard character.
Use the Join page of the SQL Assist SmartGuide to join tables in an SQL
statement. The join page displays the columns of each table selected in
the Tables page. Select displayed columns to join. You can
specify multiple joins.
Push buttons
- Alias creates an alias for the selected table and display its
columns. The displayed columns of an aliased table are available for
joining.
- Join joins the selected columns. The join is indicated
by a red join line.
- > or < navigate between multiple joins.
The selected join is indicated by a red join line.
- Unjoin removes the selected join. The join line is
removed.
- Options specifies the type of join. The type of joins
available are:
- Inner join. This is a request for rows where the values in the
joined columns match.
- Left outer join. This is a request for an inner join and any
additional rows in the left table (as viewed in the Join page) that are not
already included in the inner join.
- Right outer join. This is a request for an inner join and any
additional rows in the right table (as viewed in the Join page) that are not
already included in the inner join.
Use the Condition 1 page of the SQL Assist SmartGuide to specify a search
condition for an SQL statement. You can specify multiple search
conditions; you specify each search condition in a separately numbered
Condition page. The search conditions supplement any joins specified in
the Join page, that is, the joins and the search conditions appear in the
WHERE clause of the SQL statement.
Fields
- Select table(s) lists the tables that are available for
specifying a search condition.
- Columns lists the columns in the selected table.
- Operator lists the operators that can be specified in the
search condition.
- Values lists the values for the search condition.
Push buttons
- Find opens the Value Lookup window to find values in the
selected table column.
- Find on another column opens another Condition page (with a tab
labeled Condition 2) to specify a second search condition for the SQL
statement.
Use the Columns page of the SQL Assist SmartGuide to specify the columns
for the result set. The columns will appear in the SELECT clause of the
SQL statement.
Fields
- Select table(s) lists the tables that are available for
specifying the result set.
- Columns lists the columns in the selected table.
- Columns to include lists the columns that are included in the
result set.
Push buttons
- Add adds the selected columns to the Columns to include list
and removes the selected columns from the Columns list.
- Remove removes the selected columns from the Columns to include
list and adds the selected columns to the Columns list.
- Select all selects all the columns in the list.
- Deselect all deselects all the selected columns in the
list.
- Move up selects the column immediately above the currently
selected column in the list.
- Move down selects the column immediately below the currently
selected column in the list.
Use the Sort page of the SQLAssist SmartGuide to specify the order of rows
in the result set. You specify the order by identifying a column to be
used as a sort key. You can specify multiple columns, each one is used
as a separate sort key. The rows of the result set are ordered by the
value in the selected column, that is, by the value of the sort key. If
you specify more than one sort key, the rows of the result set are ordered by
the value of the first sort key, then by the value of the second sort key, and
so on. The sorting specification will appear in the ORDER BY clause of
the SQL statement.
Fields
- Select table(s) lists the tables that are available for
specifying the result set.
- Columns lists the columns in the selected table.
- Columns to sort on lists the columns that are used as sort keys
for ordering the result set.
Push buttons
- Add adds the selected columns to the Columns to sort on list
and removes the selected columns from the Columns list.
- Remove removes the selected columns from the Columns to sort on
list and adds the selected columns to the Columns list.
- Select all selects all the columns in the list.
- Deselect all deselects all the selected columns in the
list.
- Move up selects the column immediately above the currently
selected column in the list.
- Move down selects the column immediately below the currently
selected column in the list.
Use the Mapping page of the SQLAssist SmartGuide to remap the data
retrieved from a table column to a different SQL data type.
Fields
- Column lists the columns that are included in the result
set.
- Current data type is a read-only field that displays the
current SQL data type for the column.
- Map to new data type lists the SQL data types to which the
column can be mapped
.
Push buttons
- Use Default resets the mapping of all columns to their default
SQL data types.
Use the SQL page of the SQL Assist SmartGuide to display the SQL statement
for a Select bean. After you display the SQL statement, you can copy it
to the clipboard, run it as a test from the SQL page, or save it to a
file.
Fields
- View the SQL statement displays the SQL statement.
.
Push buttons
- Copy to clipboard copies the SQL statement to the
clipboard.
- Save SQL saves the SQL statement in a file.
- Run SQL executes the SQL statement against the database
specified in the currently selected connection alias for the Select
bean.
Specifying the Tables for an SQL Statement
Joining Tables
Specifying Search Conditions
Specifying Result Columns
Sorting the Result Set
Remapping Data to a Different SQL Data Type
Displaying the SQL statement
