sql*loader using variable, FILLER, and WHEN

I am trying to load data with variable record lengths.

The 3 column in the datafile tells me the number of columns that follow (2-5 columns)
The last column always has the same data.

.dat Example

Depending on the third column the values will be changing and it will be loading into the table as follows.
name status psn1 psn2 psn3 psn4 psn5 inv ---- ------ ---- ---- ----- ----- ----- ----- abc 12 orcl load now null null 67 xyz 12 micr bad null null null 02 pqr 13 chev gmc ford trk null 45

The 3 column in the .dat file will not be loaded, it simply tells us the number of columns that follow.

I was looking at using the FILLER property for ignoring the 3rd value and using it as a variable, but I have been unsuccessfull. Also, I was trying to use the WHEN statement as follows, but with not luck.

LOAD DATA
   INFILE 'file.dat'
   BADFILE 'file.bad'
   DISCARDFILE 'file.dsc'
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'    INTO TABLE test

      WHEN col3 = 2
         (name      CHAR(3),
          status  FILLER  NUMBER(3),
          psn1      CHAR(10),
          psn2      CHAR(10),
          inv       NUMBER(3),
          psn3      NULL,
          psn4      NULL,
          psn5      NULL)
       WHEN col3 = 3
         (name      CHAR(3),
          status   FILLER NUMBER(3),
          psn1      CHAR(10),
          psn2      CHAR(10),
          psn3      CHAR(10),
          inv       NUMBER(3),
          psn4      NULL,
          psn5      NULL)
       etc.....

Any information would be appreciated

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 29 2000 - 19:17:45 CST