Re: Question on referencing loaded columns in SQL Loader

Hi ME 8)

Doing an intermediate table is a common conversion technique, and I can see your concern. I think you can do what you want. Check your SQL*Loader manual for a section on "Applying SQL Operators to Fields"

That section explains how to reference other fields, I think you might need to use TRAILING NULLCOLS, but then you reference the other field as the replacement value. So your line would look something like this:

extracolumn "NVL( :extracolumn, :column2 )"

Note: this was not tested. And I'm not sure if you have to specify the data type in there. IOW, it might need to be:

extracolumn CHARACTER(20) "NVL( :extracolumn, :column2 )"

If I'm wrong, hopefully I still put you on the right path.

In article <7v9crf$hgp$1_at_oak.prod.itd.earthlink.net>,   "Me" <annardo_at_earthlink.net> wrote:
> Because of the application that we are using, it requires certain
columns to
> be populated with values from already loaded columns. Updates would
take a
> long time because we have about 5 million rows to load.
>
> TIA
>
> QuestionExchange <USENET_at_questionexchange.com> wrote in message
> news:2124qx_at_questionexchange.com...
> > The simple answer is NO. but more to the point -
> > To accomplish what it looks like you wish to do - load a simple
> > table with columns 1-4 and an empty 5 then use an update to
> > populate column 5. But - curiousity why are you loading 2
> > columns that are alike? At least that looks like what you are
> > doing.
> > Becca
> > > I have a question on referencing previously loaded data in
> > sql loader.
> > >
> > > e.g. I have a datafile with these data.
> > > "A1, "B1", "C1", "D1"
> > > "A2", "B2", "C2", "D2"
> > >
> > > I need to have a control file that would look something like:
> > > load data
> > > infile 'test.dat'
> > > replace
> > > into table test_table
> > > fields terminated by "," enclosed by '"'
> > > (
> > > column1,
> > > column2,
> > > column3,
> > > column4,
> > > extracolumn ":column2"
> > > )
> > >
> > > Is it possile for my fifth "extracolumn" reference the value
> > in "column2"
> > > without SQL*loader thinking that there is an extra data field
> > in the
> > > datafile? When I run the above, I'll get an error message
> > stating that
> > > "Column not found before end of record...use TRAILING
> > NULLCOLS". I don't
> > > want to use TRAILING NULLCOLS, because if I have a lot of
> > fields, then I'll
> > > get "Not all variables are bound."
> > >
> > > TIA
> > >
> > >
> > >
> > >
> > >
> > >
> >
> > --
> > This answer is courtesy of QuestionExchange.com
> >
>

http://www.questionexchange.com/showUsenetGuest.jhtml?ans_id=6328&cus_id =USE
> NET&qtn_id=6278
>
>

--
Ed Prochak
Magic Interface, Ltd.
ORACLE Development, conversions, training and support 440-498-3700 magic_at_interfacefamily.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 28 1999 - 11:35:25 CDT