If you are using the same SQL statement in multiple reports, you can create a temporary or derived table and save the SQL statement. Each time you want to use the SQL statement in a new report, you can import the statement into a new temporary or derived table and run reports against this table.
A temporary table exists in the database for the duration of your session. A derived table is saved only as an SQL select statement in the Report Designer for the duration of the session.
Whether your SQL statement creates a temporary table or a derived table depends on the database that you use. Currently only Sybase creates temporary tables. Oracle saves the SQL select statement for the table (a derived table) for the duration of the session.
Once you have created the temporary or derived table it appears in the list of tables in the Tables tab. The table name is preceded by the # symbol to identify it as temporary or derived. For information on the Tables tab, see Selecting Tables on page 127. You must also ensure the temporary or derived table is joined to the other tables you are using in your report. For information on joins, see Creating Joins between Tables on page 134.
You can create a temporary or a derived table using the Temp Table tab. This tab does not appear by default. Click the Temp Table button in the Report Designer button bar or select the menu option ModeÆTemporary Table to display the tab.
Once the temporary or a derived table has been created return to your report using the button for your report type. The temporary or a derived table appears in the Tables tab.
The Temp Table tab, shown below, contains the default SQL statement for a blank report.
|
|
Figure 83: Temp Table Tab |
The SQL statement can be created in one of two ways. You can type the SQL statement directly into the SQL statement to create temp table: field or you can build the statement using the other Report Designer tabs.
To create an SQL statement for a temporary or derived table:
1. Open a new blank report.
2. Use the Tables, Columns, Joins, Conditions and Order By tabs to specify the data that you want to be available in the temporary or derived table.
3. Open the Temp Table tab. The SQL statement to create temp table: field contains the SQL statement that matches the conditions set in the other tabs.
4. If you wish to make a change or addition return to the other tabs. Once the changes have been made, open the Temp Table tab and click the Refresh button. The SQL statement is replaced with the updated statement.
5. Enter a name for your temporary or derived table in the Temp Table Name: field.
6. Enter a schema name if required.
7. Click the Create button. You are given the option to set or change the name of the table. The temporary or derived table is added to the list of tables in the Tables tab. The name is preceded by the # symbol.
8. You can now remove the existing tables from your report and select the temporary or derived table and its columns to make the same data available.
To create a temporary or derived table by directly entering SQL statements:
1. Open the Temp Table tab.
2. Enter an SQL statement in the SQL statement to create temp table: field.
3. Enter a name for your temporary or derived table in the Temp Table Name: field.
4. Enter a schema name if required.
5. Click the Create button. You are given the option to set or change the name of the table. The temporary or derived table is added to the list of tables in the Tables tab. The name is preceded by the # symbol.
6. You can now select the temporary or derived table and its columns.
Once you have created an SQL statement you can save it for future use. Only the SQL statement is saved. The temporary or derived table name and schema name are not saved.
To open an existing SQL statement, click the Import... button. The Import a SQL Query window is displayed listing all of the previously saved SQL statements. These files are stored in the $INFOHOME/websqlbr/sql directory. Select a statement and click OK. You must enter a temporary or derived table name.
To save an SQL statement click the Export... button. The Export Query window is displayed. Enter a file name and click OK. The SQL statement is saved in the $INFOHOME/websqlbr/sql directory.
When you create a report using one or more temporary or derived tables it is not possible to view the SQL statements for these tables in the SQL statement to create temp table field. When you select the Temp Table tab the field shows the SQL statement for the current report.
To view the SQL statements for each of the temporary or derived tables, open the Temp Table tab and click the Temp Table Definitions... button. The Temporary Table Definitions window is displayed, as shown below.
|
|
Figure 84: Temporary Table Definitions Window |
Select the temporary or derived table to view using the Temp Tables drop-down list. You cannot edit the SQL statement in this window.
If you wish to change the SQL statement in the temporary or derived table you must create a new table and replace the table in use in the report.