Re: Using SQL*Loader to normalize data during load...

The easiest is to load the raw data into a conversion table and then use another program or script to populate the normalised table. That's the route I would take.

 BUT, if you really want to do this with a SQL Loader script, (assuming fixed position data) try this for the inserts:

• day 1 INTO normalized_table WHEN POSITION(16:23)!=' ' ( ticker POSITION(2:5) CHAR , year POSITION(9:12) CHAR , day POSITION(16:23)CHAR )
• day 2 INTO normalized_table WHEN POSITION(27:34)!=' ' ( ticker POSITION(2:5) CHAR , year POSITION(9:12) CHAR , day POSITION(27:34)CHAR )
• and so on ....

A lot of error prone typing if you ask me, but it's your job.

ED Prochak
Magic Interface, Ltd. 440-498-3700
Consulting and contract engineering

lee <lee_at_jamtoday.com> wrote in message news:<3C279A93.1E9AEBFE_at_jamtoday.com>...
> I've never tried it, by why couldnt you put a trigger on the target table (the one sql loader is loading) to
> execute whatever procedure you like to inert into some OTHER
> table?
>
>
> MarkyG wrote:
>
> > Maybe in Oracle 12i ;-)
> >
> > No function exists in SQL*Loader. Unless you build some kind of
> > function based around the field you want to insert and call it in the
> > SQL*Loader script.
> >
> > M
> >
> > jhardin_at_vestek.com (Jay Harding) wrote in message news:<a8ee054f.0112171442.6cc1f36a_at_posting.google.com>...
> > > I am wondering if there is a way to normalize source data during
> > > SQL*Loader load.
> > >
> > > Suppose for example the source data represents stock prices and
> > > looks something like the following(doesn't matter if it's fixed
> > > with or delimited):
> > >
> > > "AAAX","1994","14.20312","13.90625","14.01562",...,"15.93750"
> > > "BBBY","1997","20.14062","19.85938","19.84375",...,"21.03125"
> > > ... (etc)
> > >
> > > The data fields in each of these records would represent the following:
> > > TICKER,YEAR,DAY_1_PRICE,DAY_2_PRICE,DAY_3_PRICE,...,DAY_366_PRICE
> > >
> > > Where "DAY_1_PRICE" means "the price on Jan 1", and "DAY_2_PRICE"
> > > means "the price on Jan 2", ... and "DAY_366_PRICE" means, "the price
> > > on Dec 31".
> > >
> > > Ideally, I would like the loaded result set to look something like:
> > >
> > > "AAAX", "Jan-01-1994", "14.20312"
> > > "AAAX", "Jan-02-1994", "13.90625"
> > > "AAAX", "Jan-03-1994", "14.01562"
> > > ...
> > > "AAAX", "Dec-31-1994", "15.93750"
> > > "BBBY", "Jan-01-1997", "20.14062"
> > > "BBBY", "Jan-02-1997", "19.85938"
> > > "BBBY", "Jan-03-1997", "19.84375"
> > > ...
> > > "BBBY", "Dec-31-1997", "21.03125"
> > >
> > > So, what I'm wondering is if this sort of transformation is possible with
> > > SQL*Loader???
> > >
> > > Could I do something like:
> > >
> > > LOAD DATA
> > > INFILE 'blah.dat'
> > > APPEND INTO TABLE denormalized_prices
> > > (
> > > ticker CHAR TERMINATED BY ',' ENCOLSED BY '"',
> > > year CHAR TERMINATED BY ',' ENCOLSED BY '"',
> > > day_1 CHAR TERMINATED BY ',' ENCOLSED BY '"'
> > > "normalized_insert(:ticker, :year, :day_1)"
> > > day_2 CHAR TERMINATED BY ',' ENCOLSED BY '"'
> > > "normalized_insert(:ticker, :year, :day_2)"
> > > day_3 CHAR TERMINATED BY ',' ENCOLSED BY '"'
> > > "normalized_insert(:ticker, :year, :day_3)"
> > > ...
> > > day366 CHAR TERMINATED BY ',' ENCOLSED BY '"'
> > > "normalized_insert(:ticker, :year, :day_366)"
> > > )
> > >
> > > Here, "normalized_insert" would be a simple custom function
> > > which inserts a record for its given arguments???
> > >
> > > Any suggestions would be greatly appreciated.
> > > Thanks!
Received on Wed Dec 26 2001 - 12:46:21 CST