Using CONTINUEIF to Assemble Logical Records

Use CONTINUEIF if the number of physical records to be combined varies. The CONTINUEIF clause is followed by a condition that is evaluated for each physical record, as it is read. For example, two records might be combined if a pound sign (#) were in byte position 80 of the first record. If any other character were there, then the second record would not be added to the first.

The full syntax for CONTINUEIF adds even more flexibility:

Table 9-2 describes the parameters for the CONTINUEIF clause.


Table 9-2 Parameters for the CONTINUEIF Clause

Parameter Description

THIS

If the condition is true in the current record, then the next physical record is read and concatenated to the current physical record, continuing until the condition is false. If the condition is false, then the current physical record becomes the last physical record of the current logical record. THIS is the default.

NEXT

If the condition is true in the next record, then the current physical record is concatenated to the current logical record, continuing until the condition is false.

operator

The supported operators are equal (=) and not equal (!= or <>).

For the equal operator, the field and comparison string must match exactly for the condition to be true. For the not equal operator, they can differ in any character.

LAST

This test is similar to THIS, but the test is always against the last nonblank character. If the last nonblank character in the current physical record meets the test, then the next physical record is read and concatenated to the current physical record, continuing until the condition is false. If the condition is false in the current record, then the current physical record is the last physical record of the current logical record.

LAST allows only a single character-continuation field (as opposed to THIS and NEXT, which allow multiple character-continuation fields).

pos_spec

Specifies the starting and ending column numbers in the physical record.

Column numbers start with 1. Either a hyphen or a colon is acceptable (start-end or start:end).

If you omit end, then the length of the continuation field is the length of the byte string or character string. If you use end, and the length of the resulting continuation field is not the same as that of the byte string or the character string, then the shorter one is padded. Character strings are padded with blanks, hexadecimal strings with zeros.

str

A string of characters to be compared to the continuation field defined by start and end, according to the operator. The string must be enclosed in double or single quotation marks. The comparison is made character by character, blank padding on the right if necessary.

X'hex-str'

A string of bytes in hexadecimal format used in the same way as str.X'1FB033' would represent the three bytes with values 1F, B0, and 33 (hexadecimal).

PRESERVE

Includes 'char_string' or X'hex_string' in the logical record. The default is to exclude them.


The positions in the CONTINUEIF clause refer to positions in each physical record. This is the only time you refer to positions in physical records. All other references are to logical records.

For CONTINUEIF THIS and CONTINUEIF LAST, if the PRESERVE parameter is not specified, then the continuation field is removed from all physical records when the logical record is assembled. That is, data values are allowed to span the records with no extra characters (continuation characters) in the middle. For example, if CONTINUEIF THIS(3:5)='***' is specified, then positions 3 through 5 are removed from all records. This means that the continuation characters are removed if they are in positions 3 through 5 of the record. It also means that the characters in positions 3 through 5 are removed from the record even if the continuation characters are not in positions 3 through 5.

For CONTINUEIF THIS and CONTINUEIF LAST, if the PRESERVE parameter is used, then the continuation field is kept in all physical records when the logical record is assembled.

CONTINUEIF LAST differs from CONTINUEIF THIS and CONTINUEIF NEXT. For CONTINUEIF LAST, where the positions of the continuation field vary from record to record, the continuation field is never removed, even if PRESERVE is not specified.

Example 9-3 through Example 9-6 show the use of CONTINUEIF THIS and CONTINUEIF NEXT, with and without the PRESERVE parameter.

Example 9-3 CONTINUEIF THIS Without the PRESERVE Parameter

Assume that you have physical records 14 bytes long and that a period represents a space:

        %%aaaaaaaa....
        %%bbbbbbbb....
        ..cccccccc....
        %%dddddddddd..
        %%eeeeeeeeee..
        ..ffffffffff..

In this example, the CONTINUEIF THIS clause does not use the PRESERVE parameter:

CONTINUEIF THIS (1:2) = '%%'

Therefore, the logical records are assembled as follows:

        aaaaaaaa....bbbbbbbb....cccccccc....
        dddddddddd..eeeeeeeeee..ffffffffff..

Note that columns 1 and 2 (for example, %% in physical record 1) are removed from the physical records when the logical records are assembled.

Example 9-4 CONTINUEIF THIS with the PRESERVE Parameter

Assume that you have the same physical records as in Example 9-3.

In this example, the CONTINUEIF THIS clause uses the PRESERVE parameter:

CONTINUEIF THIS PRESERVE (1:2) = '%%'

Therefore, the logical records are assembled as follows:

        %%aaaaaaaa....%%bbbbbbbb......cccccccc....
        %%dddddddddd..%%eeeeeeeeee....ffffffffff..

Note that columns 1 and 2 are not removed from the physical records when the logical records are assembled.

Example 9-5 CONTINUEIF NEXT Without the PRESERVE Parameter

Assume that you have physical records 14 bytes long and that a period represents a space:

        ..aaaaaaaa....
        %%bbbbbbbb....
        %%cccccccc....
        ..dddddddddd..
        %%eeeeeeeeee..
        %%ffffffffff..

In this example, the CONTINUEIF NEXT clause does not use the PRESERVE parameter:

CONTINUEIF NEXT (1:2) = '%%'

Therefore, the logical records are assembled as follows (the same results as for Example 9-3).

        aaaaaaaa....bbbbbbbb....cccccccc....
        dddddddddd..eeeeeeeeee..ffffffffff..

Example 9-6 CONTINUEIF NEXT with the PRESERVE Parameter

Assume that you have the same physical records as in Example 9-5.

In this example, the CONTINUEIF NEXT clause uses the PRESERVE parameter:

CONTINUEIF NEXT PRESERVE (1:2) = '%%'

Therefore, the logical records are assembled as follows:

        ..aaaaaaaa....%%bbbbbbbb....%%cccccccc....
        ..dddddddddd..%%eeeeeeeeee..%%ffffffffff..

See Also:

Case study 4, Loading Combined Physical Records, for an example of the CONTINUEIF clause. (See "SQL*Loader Case Studies" for information on how to access case studies.)