RE: Sqlloader on VSAM file

[Burton, Laura L.]

Title: RE: Sqlloader on VSAM file

Raymond,

 

Please forgive me, but I am not sure now if you are asking a question or stating that you're ok.  Your control file looks ok except I 'define' my fields differently.  I am sending you an example below.  I am assuming your amount fields has 2 decimal positions.  Let me know if you need anything else.

Laura

_______________________________________________________

options (errors=99)
load data
infile 'pmiwkly.dat'
truncate

 

into table pmiwkly
trailing nullcols
(
badge                         position(01- 06)         char,
wedte                         position(07- 12)         date
    "MMDDYY"
    "decode(:WEDTE, '000000', null, :WEDte)",
mgmtded                    position( 17- 17)        CHAR,
unionded                    POSITION( 18- 18)     CHAR,
esophrs                      POSITION( 78- 82)     INTEGER EXTERNal
        ":ESOPHRS/10",
esopamt                     POSITION( 83- 89)     INTEGER EXTERNAL
        ":ESOPAMT/100",
mesophrs                    POSITION( 90- 94)     INTEGER EXTERNAL
        ":MESOPHRS/10",
mesopamt                   POSITION( 95-101)     INTEGER EXTERNAL
        ":MESOPAMT/100"

)

_________________________________________________________

-----Original Message-----
From: Raymond Lee Meng Hong [mailto:[EMAIL PROTECTED]]
Sent: Sunday, June 10, 2001 9:25 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Sqlloader on VSAM file

[Raymond]  I did check on the source text file , I did't see a carriage return on this, according to the previous example , this is the 1 record

A38001000307754A0000005516562HATIJAH BT
BAKAR                        19920130020000020000
0000850

this is the 2nd record and so on .and for the first 6 character is the agent code , which will not be the same like A38001, might be something else F41010..or whatever ..

A38001053088697A0000006106290ALIBABAKA
R                        1992013001000001000000008
50

here is my control files. . if the record is like this 1 line/record.

A38001053088697A0000006106290ALIBABAKAR                         199201300100000100000000850

LOAD DATA
INFILE AAA.DAT
APPEND INTO
TABLE LN21PNB
(FINANCE_CD        position(1:6)DECIMAL EXTERNAL,
 MEMBERSHIP_NO        position(7:15)CHAR,
 ICNO            position(16:29)CHAR,
 NAME        position(30:69)CHAR,
 CERTNO       position(70:75) CHAR,
 TXN_DATE     position(76:83)DATE 'YYYYMMDD',
 LOAN_AMT        position(84:89)DECIMAL EXTERNAL,
 REPAY_AMT     position(90:96) DECIMAL EXTERNAL,
 REASON_CD     position(97:98) DECIMAL EXTERNAL,
 REPAY_MAG     position(99:102) DECIMAL EXTERNAL)

sorry for any misleading