Re: Using SQL*Loader

Carol Kilner wrote:
-

• I'm trying to do the following using SQL*Loader. Can it be done.
  -
  
• If have the following input file
  -
  
• SLSMNO1 Salesman Name # 1
• SLSMN02 Salesman Name # 2
• INDGRP1 Industry Group # 1
• INDGRP2 Industry Group # 2
• TERR1 Territory desc # 1
• TERR2 Territory desc # 2
• ...you get the idea.
  -
  
• If positions (1:6) = "SLSMNO" I want to insert those records
• into say table # 1. The catch is I want to insert 2 fields one
• with the code 1 and the other with the description. I don't want to
• actually insert SLSMNO in any field -- as is the example in the oracle
• manual.
  -
  
• Similary, if positions (1:5) = "TERR"
  -
  
• I would appreciate any ideas on this one. One solution I have come up with is an intermediate table and
• then use SQL to load the final table...no exactly a neat solution.
  -
  
• Thanks,
  -
  
• Carol Kilner
• BASF Canada

You can use constants.

LOAD DATA
INFILE myfile.dat
INTO TABLE sales when (1:6) = 'SLSMNO'
( id CONSTANT 1,
  description POSITION (12:31) VARCHAR) INTO TABLE terr WHEN (1:4) = 'TERR'
( id CONSTANT 2,
  description POSITION (12:31) VARCHAR)

If what you really wanted was that the TERR1 record puts a 1 into the ID field and the TERR2 record puts a 2 into ID, replace the CONSTANT clause with POSITION(5:5).

I hope this helps.

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com