Re: Trim Leading Spaces and Loading Blanks for Number fields

Val wrote:

> I am trying to load in a column that is defined as a number in the
> table. In the dat file the field is coming in with leading spaces or
> blanks. The error message I get when trying to load in the blanks or
> leading spaces with value is "invalid number". how can I satisfy the
> two conditions. I tried doing this:
>
> FIELD_A NULLIF FIELD_A=BLANKS "LTRIM(:FIELD_A)"
Run the following demo for an example:

LOAD DATA
INFILE *
INSERT
INTO TABLE emp
(

empno   POSITION(01:04) INTEGER EXTERNAL NULLIF empno=BLANKS,
ename   POSITION(06:15) CHAR,
job     POSITION(17:25) CHAR,
mgr     POSITION(27:30) INTEGER EXTERNAL NULLIF mgr=BLANKS,
sal     POSITION(32:39) DECIMAL EXTERNAL NULLIF sal=BLANKS,
comm    POSITION(41:48) DECIMAL EXTERNAL NULLIF comm=BLANKS,
deptno  POSITION(50:51) INTEGER EXTERNAL NULLIF deptno=BLANKS)

BEGINDATA
7781 CLARK      MANAGER   7838 2572.50           10
7839 KING       PRESIDENT      5500.00           10
7934 MILLER     CLERK     7782  920.00           10
7566 JONES      MANAGER   7839 3123.75           20
7499 ALLEN      SALESMAN  7698 1600.00   300.00  30
7654 MARTIN     SALESMAN  7698 1312.50  1400.00  30
7658 CHAN       ANALYST   7566 3450.00           20

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)