SQL Loader...concatenate fields

[mike777]

Hello folks!

Does anyone know of a way to concatenate two fields when loading via SQL Loader?

I have data that looks like:

ID,Area_Code,Phone_Number
1,704,1234560
2,305,7891230
3,904,456890

I have a table that has a PHONE column, but no columns for area_code and phone_only. My control file looks something like:

load data
infile '\\someplace\somewhere\somefile.dat'
append into table sometable
fields terminated by ',' optionally enclosed by '"' trailing nullcols
(id,
filler1 filler,
filler2 filler,
phone ":filler1||:filler2")

Problem is (it seems), once you designate a column as a "filler", all of a sudden you can't use it in a SQL statement on load, like I tried above. (If I do create a staging table that actually has four columns (id,area_code,phone_only,phone) and change "filler" to "char", then the approach above works fine.)

Now, I know I could just create a staging table, and then use SQL to transfer the data over to the final table. Or, I could just add the columns to the existing table, then drop them. But I'm wondering if there's a couple steps I could save with some fancy SQL*Loader code.

I checked on the web, and came up empty-handed.

Thanks for your help.

-Mike

 

[taupirho]

Hi,

Instead of a staging table with 4 columns try creating a view on your table but with a dummy 4th column. Now change your fillers to char and sqlload your data into the view as you would with your 4 column staging table.

 

[mike777]

hi tom,
wow, if that works, you'll be a genius in my book!
i'll try tomorrow (tues) at work.
thanks a million.
-mike