Oracle7 Server Utilities

Applying SQL Operators to Fields

A wide variety of SQL operators may be applied to field data with the SQL string. This string may contain any combination of SQL expressions that are recognized by Oracle as valid for the VALUES clause of an INSERT statement. In general, any SQL function that returns a single value may be used. See the section "Expressions"

, "Operators, Functions, Expressions, Conditions", in the Oracle7 Server SQL Reference.

The SQL string must be enclosed in double quotation marks. It appears after any other specifications for a given column. It is evaluated after any NULLIF or DEFAULTIF clauses, but before a DATE mask. It may not be used on RECNUM, SEQUENCE, CONSTANT, or SYSDATE fields. If the RDBMS does not recognize the string, the load terminates in error. If the string is recognized, but causes a database error, the row that caused the error is rejected.

Referencing Fields

To refer to fields in the record, precede the field name with a colon (:). Field values from the current record are substituted. The following examples illustrate references to the current field:

field1 POSITION(1:6) CHAR "LOWER(:field1)"

field1 CHAR TERMINATED BY ','

       NULLIF ((1) = 'a') DEFAULTIF ((1)= 'b')

       "RTRIM(:field1)"

field1 CHAR(7) "TRANSLATE(:field1, ':field1', ':1')"

In the last example, only the :field1 that is not in single quotes is interpreted as a column name. For more information on the use of quotes inside quoted strings, see "Specifying Filenames and Database Objects" .

Other fields in the same record can also be referenced, as in the following example:

field1 POSITION(1:4) INTEGER EXTERNAL

		"decode(:field2, '22', '34', :field1)

Common Uses

Loading external data with an implied decimal point:

field1 POSITION(1:9) DECIMAL EXTERNAL(8) ":field1/1000"

Truncating fields that could be too long:

field1 CHAR TERMINATED BY "," "SUBSTR(:field1, 1, 10)"

Combinations of Operators

Multiple operators can also be combined, as in the following examples:

field1 POSITION(*+3) INTEGER EXTERNAL

       "TRUNC(RPAD(:field1,6,'0'), -2)"

field1 POSITION(1:8) INTEGER EXTERNAL

       "TRANSLATE(RTRIM(:field1),'N/A', '0')"

field1 CHARACTER(10)

       "NVL( LTRIM(RTRIM(:field1)), 'unknown' )"

Use with Date Mask

When used with a date mask, the date mask is evaluated after the SQL string. A field specified as:

field1 DATE 'dd-mon-yy' "RTRIM(:field1)"

would be inserted as:

TO_DATE(RTRIM(<field1_value>), 'dd-mon-yyyy')

Interpreting Formatted Fields

It is possible to use the TO_CHAR operator to store formatted dates and numbers. For example:

field1 ... "TO_CHAR(:field1, '$09999.99')"

could store numeric input data in formatted form, where field1 is a character column in the database. This field would be stored with the formatting characters (dollar sign, period, and so on) already in place.

You have even more flexibility, however, if you store such values as numeric quantities or dates. You can then apply arithmetic functions to the values in the database, and still select formatted values for your reports.

The SQL string is used in Case 7 () to load data from a formatted report.