SQLLDR-field data in subselect in control file?
Date: 6 Mar 2003 10:14:06 -0800
Message-ID: <cf2ba974.0303060819.6bf23bec_at_posting.google.com>
Hi,
[Quoted] I need to load a CSV file that contains an Manufacturer Name (VARCHAR) but I need to store an Manufacturer ID (NUMBER) in the table specified in my sqlldr control file.
Is it possible to have the control file look up the Manufacturer ID based on the Manufacturer Name found in each row of the CSV file? The two tables are related as one-to-many, one manufacturer record can have many equipment records. The equipment table has a foriegn key pointing to the manufacturer table.
I've tried doing a sub select and tried making a User Defined Function but am having no luck. I've also tried creating a view that joins the two tables and having the control file inster into that view, but it complains about how it cant insert into a view with two tables.
Thanks in advance!
Eric.
Here's what my control file looks like:
LOAD DATA
INFILE EvpDB.csv
BADFILE 'bad_EvpDB.csv'
-- APPEND
REPLACE
INTO TABLE equip_temp
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( EQUIPMENT_ID "EQUIPMENT_TEMP_ID_SEQ.nextval", TYPE_ID CONSTANT '4',WHERE manufacturer_name=(:MANUFACTURER_NAME)",
-- MANUFACTURER_ID "my_pkg.manuf_id(:MANUFACTURER_NAME)",
-- MANUFACTURER_ID "select manufacturer_id from manufacturer
MODEL_TRADE_NAME ,
EVAP_MODEL_NUMBER ,
EVAP_INDUSTRY_STANDARD_RATING,
DuplicateEvapIndStdRating FILLER,
EVAP_MOTOR_HORSEPOWER FILLER,
Speed FILLER, EVAP_EFFICIENCY FILLER, CREATE_BY_ID CONSTANT '1', CREATE_DATE SYSDATE, LAST_MOD_BY_ID CONSTANT '1', LAST_MOD_DATE SYSDATE
) Received on Thu Mar 06 2003 - 19:14:06 CET