Importing SQL Files

The Import SQL file feature allows you to take a complete SQL statement from a text file, import it into a query, and retrieve the data set from the database server. When the file is imported, it is scanned to determine the number of columns that will be returned by the SQL, with the request line becoming populated with a column indicator for each of the columns. Using this feature, you can take advantage of SQL statements you have already written.

Before importing a SQL file, verify the following:

You are connected to a database server and are working in the Query section.
There must be no tables in the Contents pane.
The SQL file to be imported must begin with a SELECT statement, and you should know the number of columns to be displayed in the Results section.

Once the SQL file is imported into the query, you cannot edit it, drag items from table onto the Request line (however, you can specify a user-friendly name for the Request line item and identify its data type), use the custom SQL feature, or display its properties.

To import an SQL file:

Select File, then Import Data File, and then SQL.
The Import SQL File dialog box is displayed.
Select the desired SQL file and click Open.
In the Number of Columns dialog box, enter the number of columns that you want the query to run and click OK.
For extremely complex SQL statements, Interactive Reporting needs to be advised on how many columns to expect from the database. The default number in the dialog box is the "best guess" option.
Interactive Reporting inserts the SQL statement directly into the content, nested between the header and footer "Imported SQL Statement. " If the statement is larger than the entire Contents pane use the scroll keys to view it.
Double-click a Request line item.
You can also click a column on the Request line and click Properties on the shortcut menu.
The Item Properties dialog box is displayed.
Specify a name for the item in the Name field.
Associate the item with a data type by selecting a data type from the pull-down list and click OK.
Click OK to retrieve the results.
The column data types default to string unless they are manually changed.

To refresh or delete a column name:

Edit the SQL in a text editor, such as Windows Notepad or vi in UNIX and save it.
In the Query section, right-click on the area labeled '--Imported Sql Statement--'.
Select either Refresh or Delete as needed.

To edit SQL that has no associated file:

Select View , then Query Log.
Process the query containing the imported SQL text.
Copy the SQL statement from the Query Log window and paste it into a new text file using your editor.
When you refresh an imported SQL statement, Interactive Reporting attempts to open the same file used when you first imported the statement. If that file no longer exists, you are prompted to select a new file. Because editing the SQL statement might change the SELECT list (which appears on the Request line) you must redo any edits made to the name and/or data type of those items after refreshing the SQL statement.