Hi,
I have a number of new and replacement fields I need to add to an existing Oracle table with SQL Loader. Currently I have a csv file containing 11 fileds and this is loaded into a 21 column table using SQL Loader. The control file loads these 11 fields from the csv file, another four table columns are populated with loader calulations and the rest are handled with the FILLER cluse.
…(
FILLER_1 FILLER,
NMI “substr(:NMI,1,10)”,
FILLER_3 FILLER,
…
FILLER_10 FILLER,
normal_or_cancel,
meter_no “to_char(to_number(substr(:meter_no,2)))”,
suffix ,
component,
start_date date ‘yyyy-mm-dd’,
adjust_indicator “case when :normal_or_cancel = ‘C’
then ‘N’
else null
end”,
end_reading_date date ‘yyyy-mm-dd’,
end_date " to_date(:end_reading_date,‘yyyy-mm-dd’)-1 ",
creation_date " sysdate ",
FILLER_20 FILLER,
days_connected,
quantity "case when to_number(substr(:component,1,1)) <= 2
then to_number(:quantity)
else 0
end ",
retail_tariff_code,
status “case when :normal_or_cancel = ‘C’
then ‘J’
else ‘P’
end”,
max_demand "case when to_number(substr(:component,1,1)) <= 2
then null
else :quantity
end ",
load_file_id CONSTANT {LOADFILEID}
)…
The new csv file contains 27 fields and this needs to be loaded into the same table using SQL Loader. The new csv file now has some fields that weren’t previously available, which will replace existing fields/columns that were populated with the calulations or were delt with by the FILLER cluse. The control file excerpt below (* indicates new, **indicates replaced) has been updated to load these 26 fields from the csv file and two table columns are populated with loader calulations. What I would like to know; is it possible to rename and replace the existing fields, calculations and FILLER cluses as I’m attempting to do below or is it best to leave existing columns in the Table as they are and add new fields and update the control file to suit?
…
INTO TABLE
ntb_consump_t1_basic
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘"’ TRAILING NULLCOLS
(
*meter_type,
NMI “substr(:NMI,1,10)”,
*event_no,
*cancelled_event_no,
*bill_processed_date,
*cancelled_event_bill_date date ‘yyyy-mm-dd’,
*tariff_category,
*validation_code,
*register_no,
*consump_type,
*normal_or_cancel,
*meter_no “to_char(to_number(substr(:meter_no,2)))”,
suffix,
**is_a_demand_record,
start_date date ‘yyyy-mm-dd’,
**start_reading,
end_reading_date date ‘yyyy-mm-dd’,
*end_reading,
**end_date date ‘yyyy-mm-dd’,
billing_period_days,
days_connected,
quantity "case when to_number(substr(:component,1,1)) <= 2
then to_number(:quantity)
else 0
end ",
retail_tariff_code,
**max_demand "case when to_number(substr(:component,1,1)) <= 2
then null
else :quantity
end ",
num_of_outlets,
retail_account_no,
component_code,
load_file_id CONSTANT {LOADFILEID}
)
…
COLUMNS of table ntb_consump_t1_basic -
READ_ID:
NMI:
RETAIL_TARIFF_CODE:
START_DATE:
END_DATE:
QUANTITY:
CREATION_DATE:
BILL_ID:
STATUS:
PARENT_ID:
ADJUST_INDICATOR:
ADJUSTMENT_REASON:
ENTERED_BY:
MAX_DEMAND:
SUFFIX:
COMPONENT:
LOAD_FILE_ID:
DAYS_CONNECTED:
NORMAL_OR_CANCEL:
END_READING_DATE:
METER_NO: