Using output redirection

Output redirection can be used to export query results.

You can redirect the output of any command to a file or device by putting the ># redirection symbol anywhere on the command. The redirection symbol must be followed by a file name. (In a command file, the file name is then followed by the semicolon used as statement terminator.) The file is placed relative to the directory where Interactive SQL was started.

In this example, output is redirected to the file empfile:

SELECT *
FROM Employees
># empfile

Do not enclose the file name in quotation marks.

Output redirection is most useful on the SELECT statement.

Use two > characters in a redirection symbol instead of one (for example, >>#), to append the output to the specified file instead of replacing the contents of the file. Headings are included in the output from the SELECT statement if the output starts at the beginning of the specified file and the output format supports headings.

Redirecting output and messages

The >& redirection symbol redirects all output including error messages and statistics for the command on which it appears. For example:

SELECT *
FROM Employees 
>& empfile

Do not enclose the file name in quotation marks.

This example outputs the SELECT statement to the file empfile, followed by the output from the SELECT statement and some statistics pertaining to the command.

The >& redirection method is useful for getting a log of what happens during a READ command. The statistics and errors of each command are written following the command in the redirected output file.

NULL value output

The most common reason to extract data is for use in other software products. The other software package may not understand NULL values, however.

The dbisql option NULLS allows you to choose how NULL values are output. Alternatively, you can use the IFNULL function to output a specific value whenever there is a NULL value.

For information on setting dbisql options, see Chapter 2, “Database Options,” in Reference: Statements and Options.