Re: SQLLDR-field data in subselect in control file?

My advice is to do that in a "before trigger". For each row that you are inserting you will be retrieving the Manufacturer ID from the lookup table using the manufacturer name.

Trigger Insert_Item before insert on equip_temp for each row
begin

    Select Manufacturer_ID

        into :new.Manufacturer_ID
      from manufacturer
     where manufacturer_name=:new.manufacturer_name;

Piece of cake.

Note: this trigger will be fired on *any* insert, even when some user is updating the table from client screen for instance, but theoretically it should cause no damage to the data, in the end it ensures that the proper manufacturer id is attached to a certain manufacturer name. If this is a staging table used only during data load then no problem at all.

Bye,
Flavio

"Eric Walstad" <ewalstad_at_yahoo.com> wrote in message news:cf2ba974.0303060819.6bf23bec_at_posting.google.com...
> Hi,
>
> 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',
> -- MANUFACTURER_ID "my_pkg.manuf_id(:MANUFACTURER_NAME)",
> -- MANUFACTURER_ID "select manufacturer_id from manufacturer
> WHERE manufacturer_name=(:MANUFACTURER_NAME)",
> 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:33:40 CET