SQL*LOADER-LOOKUP/REPLACE VALUES

Hi!

We're running Oracle V7.0.16.4.0 on Solaris 2.3. I'm creating a control file to handle data entry from non-delimited text files using SQL*LOADER. The SQL script seems to be a handy tool for replacing one data value with another. I seem to be having a problem, however, when I try to reference another table as a "lookup" for a value replacement.

EXAMPLE: The person submitting a survey data form codes his/her INITIALS on the form. (Simple Simon would code SS.) The initials are scanned (with other data on the form) into a text file.

Personal information about each submitter (name, address, initials, employer, etc.) will be stored in a reference table, CHARACTERS, which will use a sequential number as each character's ID (Primary Key). Unique initials will be assigned to each employee within an organization.

The text file will be loaded into the table PIE. There is a column SUBMITTER in the table PIE which stores the submitter's ID number, referencing the ID in the CHARACTERS table.

When I load the text file into the table PIE, the column_spec looks something like this:

(SUBMITTER POSITION(3:5) CHAR NULLIF id = BLANKS "SELECT id FROM CHARACTERS WHERE INITIALS = :SUBMITTER)

I get an error message that there is a missing expression, and SQL*LOADER cannot parse the insert statement.

My question is: Can I use something like the statement above to translate a value from a referenced table before loading data into a table? If not SELECT, is there another command or function that will work?

Any help will be greatly appreciated. Received on Tue May 17 1994 - 15:32:26 CEST