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 |
---|---|
|
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. |
|
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. |
|
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. |
|
This test is similar to
|
|
Specifies the starting and ending column numbers in the physical record. Column numbers start with 1. Either a hyphen or a colon is acceptable ( If you omit |
|
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. |
|
A string of bytes in hexadecimal format used in the same way as |
|
Includes ' |
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..
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.)