The SQL Wizard allows you to build an SQL statement, send it to a remote database server, retrieve the resulting data, if any, save the resulting data, and save the SQL statement for re-use.
The remote database server must be running on an iSeries or AS/400, and an appropriate Java Database Connectivity (JDBC) driver must be present on the client workstation. The Host On-Demand client and the Database On-Demand client already include a JDBC driver from the AS/400 Toolbox for Java, which allows the Host On-Demand client or Database On-Demand client to access DB2/400 data on a properly configured iSeries or AS/400 (see the Driver identifier field on the Logon tab).
Database servers running on other host platforms are supported only if the appropriate JDBC driver is installed on the Host On-Demand or Database On-Demand client workstation (see the Driver identifier field on the Logon tab).
The SQL Wizard's user interface helps you to select the tables to work on, to select the columns from which data is to be returned, to specify conditions for selecting the rows to be processed, to select the columns on which the returned data is to be sorted, and to build a valid SQL statement. A new Expression Builder lets you use operators, predefined functions, constants, and programming constructs to define complex conditions for selecting the rows to be processed. The same Expression Builder lets you define column expressions that automatically perform and display the results of complex computations based on returned values. Returned data (including the results of column expressions) can be either displayed or written into a file. Various file formats are supported, including XML. SQL statements can be labeled and saved for re-use.
You can set default values for some of the fields in this wizard, if your administrator has configured your client or your user id to do so:
A sequence of tabs guides you through the process of building and running an SQL statement. These tabs are:
If the SQL Wizard is launched from a 5250 Display session, then the SQL Wizard initially displays in the Database URL field a default database URL containing the name of the current iSeries host.
For general information on using a JDBC driver with the Host On-Demand client or the Database On-Demand client, see Registering a JDBC driver.
COM is different
from com).
If you do not know the fully qualified class name,
contact the provider of the driver.
The driver must be installed on the client workstation and must be accessible to the Host On-Demand client or Database On-Demand client (see Deploying a JDBC driver).
Type a comma-separated list of schema names, schema filters, table names, and table filters. For assistance, see Examples. For a detailed description of schema names, schema filters, table names, and table filters, see Specifying a list of available schemas and tables.
The comma-separated list that you type into this field specifies the schemas and tables that you want to appear in the List of available tables on the Tables tab of the SQL Wizard after you log on to the database.
To use the default value, leave this field blank.
This Table Filter field is one of several levels of filtering for the list of available tables (see Level 4. Value for the current SQL or File Upload statement).If the connection fails and you see a Java error message or are running Netscape 4.7x, see Problems logging on or connecting.
In certain circumstances you may encounter
the error message SQL Assist Exception.
See SQL Assist Exception.
Click Disconnect to disconnect from the remote database server.
The contents of this list are determined by several levels of filtering (see Levels of filtering for schema names and table names).
You will notice that:
To move one or more tables to the list of Selected tables:
To expand or collapse the contents of a schema in the list,
click the + or - symbol to the left of the schema name.
If the type of the SQL statement is Select, the SQL Wizard adds the names of the selected tables to the FROM clause of the SQL statement on the Review tab.
A correlation name is an alternate name for a table. When you select the same table (for example, AA01) more than once, the SQL Wizard automatically creates a correlation name (for example, AA01_1). To create a correlation name manually:
To move one or more table names back to the list of Available tables:
To move all tables back to the list of Available tables,
click << .
Use the Columns tab to select the columns that you want included in the data returned by the SQL statement. The Columns tab is available only when the type of the SQL statement is Select.
When you select a column on the Columns tab, the SQL Wizard adds the column name to the SELECT clause of the SQL statement on the Review tab.
If you do not select any columns on the Columns tab,
then by default the SQL statement selects all the columns
in the table or tables that you specify.
The reason is that the default SQL statement for a Select operation
is SELECT * from tablename,
where *
means "all columns".
To select one or more columns:
+ or - symbol to the left of the table name.)
To remove one or more columns from the list of Selected columns
When you run the SQL statement, Host On-Demand displays the columns left to right on the Results tab in the same order as you specify on the Columns tab.
A column expression is an expression whose evaluated value you want to appear as a column in the result.
Column expressions are similar to operations in a software spreadsheet, because you can combine the values in the columns of a table with:
Column expressions provide an extremely powerful tool for automatically performing and displaying the results of computations based on table values.
As a very simple example, if table HODTEST.AA01 contains a column named SALES,
and you want to compute the value of a 6% sales commission
that is based on the value stored in the SALES column of each row of the table,
then you can use the Expression Builder
to build or type the expression HODTEST.AA01.SALES * .06.
When you run the SQL statement, a column of output is generated
for this column expression showing the sales commission for each
entry in the table.
You can also use multiple columns in a column expression.
For example, to add the values in the columns SALARY, BONUS,
and COMM, you could build or type the column expression
SALARY + BONUS + COMM.
For more information see Building expressions.
A join is a mechanism for selecting which rows from two related tables are included in an SQL statement, based on equality (or some other relationship, such as inequality, greater than, and so on) between the contents of selected columns.
The Joins tab is available only when the type of the SQL statement is Select.
The inner area of the Joins tab displays a small window for each of the tables that you have included in the list of selected tables on the Tables tab. In the small window for each table the names of the columns of that table are listed.
In the outer area of the Joins tab, use the buttons on the right side of the tab to create and manipulate joins.
The colored connecting lines have the following meanings:
Use the Conditions tab for the following two purposes:
The Conditions tab is available only when the type of the SQL statement is Select, Update, or Delete.
To exclude duplicate rows from the results of a Select statement, select the checkbox Exclude duplicate rows (SELECT DISTINCT). When you select this checkbox, the SQL Wizard adds the DISTINCT modifier to the SELECT verb of the SQL statement on the Review tab.
A condition is a criterion that you define for the remote database server to use in selecting rows from the tables that you have included in the list of selected tables on the Tables tab. You can specify one or more conditions. If a row meets all the conditions that you specify, then the remote database server includes that row in the operation (Select, Update, or Delete).
Use this tab to specify all the conditions for a single SQL statement.
You can use the controls to build a condition, or you can type a condition directly into the Conditions field.
Follow these steps to build a condition using the controls:
Select the column for the search from the Available columns list.
Select an operator from the Operators list.
Type one or more values in the Values list. Enter one value per line. Click Clear to remove all of the values from the Values list.
If you selected the Between operator in the Operators list, you must enter two values per line in the Values list. The values are separated by an ampersand (&).
You can search for appropriate values by clicking Find.
You can specify host variables in the Values list. To do this, click on a line in the Values list, then click Add Variable. The Add Variable window is displayed. Type the variable name, then click OK. The Add Variables window closes, and the variable is added to the Values list on the Conditions tab.
You can specify parameters in the Values list. If a parameter is specified, its value is used in the search condition. A parameter is specified in the format :parm, where parm is the parameter name. For example, :empid is a valid specification for a parameter named empid.
Click Add to add the condition to the Conditions field.
To specify additional search conditions for your statement, click And or Or. Then, build the second search condition as described in the previous steps. When you click Add, the subsequent conditions are appended to those that already are displayed in the Conditions field.
To type a condition into the Conditions field, move the input focus to the Conditions field and type the condition.
You can also use the Conditions field to modify or delete a condition that you built using the controls. To modify a condition, use the insert, delete, backspace, cursor movement, and letter keys to add or remove text. To delete a condition, use the delete key or backspace key, or follow these steps:
You can also copy and paste text between the system clipboard and the Conditions field.
To build complex conditions, click Advanced Expression to open the Expression Builder - Conditions window (see Building expressions).
Use the Groups page to specify which columns to use for grouping results. When you group results, the data in the result set is divided into groups by the values in the specified columns. One row of data is returned for each group.
The Groups tab is available only when the type of the SQL statement is a Select statement.
You can group more than one column. However, the groups will not be sorted; use the Order page to sort the result set.
To include groups in your result set:
Select the Include grouping columns checkbox. The columns that are specified in the Selected columns list on the Columns page are displayed in the Grouping columns list. These are mandatory grouping columns.
If this checkbox is selected and no columns are specified in the Selected columns list on the Columns page, then all of the available columns are displayed in the Grouping columns list.
To add more columns to the Grouping columns list:
Select one or more columns in the Available columns list. The list displays all of the columns in a particular table. You can expand or collapse a table to display or hide the columns that are included in that table.
Click > to add the columns to the Grouping columns list. Click >> to add all of the columns from all of the tables in the Available columns list to the Grouping columns list.
To remove one or more columns from the Selected columns list, click on the columns, then click <. To remove all of the columns from the Selected columns list, click <<.
To remove mandatory grouping columns from the Selected columns list on the Groups page, you must first remove those columns from the Selected columns list on the Columns page.
Filtering grouping results
You can eliminate unwanted grouped data by filtering the results that are returned by the grouping columns. To do this, either type the conditions in the Group conditions field, or click Advanced Expression to build one or more conditions.
Use the Order tab to sort the rows that appear on the Output tab. The rows on the Output tab are the rows returned by the remote database server when you run an SQL Select statement.
The Order tab is available only when the type of the SQL statement is Select.
Normally (that is, without the Order tab), the rows on the Output tab are displayed in whatever order the rows happen to occur in the table or tables to which they belong.
With the Order tab, you can select one or more columns that you want to be used for sorting the returned rows. For example, if you select a column named OBJECTID and specify ascending order, and if the contents of OBJECTID are positive numbers, then the returned rows are sorted starting with the row or rows that have the smallest value in OBJECTID (such as 00001), followed by the row or rows containing the next smallest value in OBJECTID (such as 00004), and so on.
In addition:The radio button Display output columns only is always selected. This means that the Output tab displays only columns that are included in the list of Selected columns on the Columns tab (including the results of any column expressions).
The primary use of this tab is to allow you to run the generated SQL statement. You can also do the following operations (unless the administrator has disabled the operation):
Use the Insert tab to specify the values that you want to add to the specified table. When you insert values, a row is created in the table specified on the Tables tab, and the values that you specify on the Insert tab are placed into that row.
To specify the values to insert, type the value that will be inserted for that column in the Value field.You must type a value for every non-null column. Non-null columns are indicated by a plus sign (+).
You do not have to provide values for null columns.
Use the Update tab to specify the values that you want to change in the specified table. When you update values, the values that you specify on the Update tab are placed into the table specified on the Tables tab.
To specify the values to change, in the Value field, type the value that will be updated for that column.You do not have to update all the values in a row; you only need to enter values for the columns that you want to update.
You must enter a value for at least one column.