Selecting data to unload

The following tasks describe how to select data to unload in BMC AMI Unload.

Selecting columns

Use standard SQL SELECT statements to select columns in BMC AMI Unload.

The following is a simple example of an BMC AMI Unload SELECT clause:

Related topics

UNLOAD FORMAT(UNLOAD)
SELECT COL01, COL02, 'CONSTANT', INTEGER(123)
WHERE COL02 = '20'
ORDER BY COL01
FROM NGT.UNLOAD_TEST

In the list of selected expressions, you can specify column names, functions, expressions, and constants, just as you would with SQL. You can also use the SQL CASE construct to apply logic to data and substitute values.

Example

The following is an example using two CASE constructs:

UNLOAD FORMAT(DSNTIAUL)
SELECT COL01,
        CASE COL02
           WHEN 'TX' THEN 'TEXAS'
           WHEN 'CA' THEN 'CALIFORNIA'
           WHEN 'MO' THEN 'MISSOURI'
           WHEN 'AK' THEN 'ALASKA'
           ELSE CASE
              WHEN COL02 BETWEEN '01' AND '50' THEN 'USA'
              WHEN COL02 = '51' THEN 'ITALY'
              WHEN COL02 = '52' THEN 'CANADA'
              ELSE NULL
              END
           END,
        'BMC',
        INTEGER(123)
FROM NGT.UNLOAD_TEST

You can also use concatenation, substring operations, functions, and special registers. For more information, see BMC-AMI-Utilities-SQL-language.

Important

BMC AMI Unload supports SUBSELECT clauses used within the CASE construct.

Selecting rows

By default, BMC AMI Unload unloads all table rows.

You can filter the rows to be unloaded using one of the following methods:

Adding a WHERE clause
Using the MAXROWS option
(for image copies) Selecting with an alternate OBID

You can specify a WHERE clause and the MAXROWS option simultaneously. If you specify both, BMC AMI Unload applies the WHERE clause first, then MAXROWS.

You can specify SQL WHERE clauses that contain subselects in BMC AMI Unload. For more information about BMC AMI Unload WHERE clauses, see BMC-AMI-Utilities-SQL-language.

Selecting data to unload

Selecting columns

Selecting rows

On this page