1.22 COPY : Indicating NULLs while loading Fixed width files.

COPY : Indicating NULLs while loading Fixed width files.

The default NULL string for a fixed-width load cannot be an empty string, and instead, consists of all spaces.

The number of spaces depends on the column width declared with the COLSIZES (integer, [,...]) option.

For fixed-width loads, the NULL definition depends on whether you specify NULL at the column or statement level, as follows:

• Statement level—NULL must be defined as a single-character. The default (or custom) NULL character is repeated for the entire width of the column.

• Column Level—NULL must be defined as a string whose length matches the column width.

To turn off NULLs, use the NULL AS option and specify NULL AS ''.

Following is an example of specifying NULL character at the statement level.

Note that when specifying the NULL character at the statement level it should be repeated for the entire length of the column.

<> /home/sukul1 $ vi employeedet3.dat

"employeedet3.dat" 3 lines, 117 characters

RAHUL,DRAVID       ***2010-01-01100000

SAURAV,GANGULY     1012010-10-01******

SHANE,WARNE        85 **********200000

sukul1=> COPY USER_30_DAY_TABLES.EMPLOYEE_1

NAME,

sukul1-> (

sukul1(> NAME,

sukul1(> AGE,

sukul1(> JOINING_DATE FILLER VARCHAR(10),

sukul1(> JOINING_YEAR AS TO_NUMBER(TO_CHAR(TO_DATE(JOINING_DATE,'YYYY-MM-DD'),'YYYY')),

sukul1(> SALARY

sukul1(> )

sukul1-> FROM LOCAL '/home/sukul1/employeedet3.dat'

sukul1-> FIXEDWIDTH COLSIZES(19,3,10,6)

sukul1-> NULL '*'

sukul1-> DIRECT;

 Rows Loaded

-------------

           3

(1 row)

sukul1=> select * from USER_30_DAY_TABLES.EMPLOYEE_1;

        NAME         | AGE | JOINING_YEAR | SALARY

---------------------+-----+--------------+--------

 RAHUL,DRAVID        |     |         2010 | 100000

 SHANE,WARNE         |  85 |              | 200000

 SAURAV,GANGULY      | 101 |         2010 |

(3 rows)

sukul1=>

Note that if the null character does not occupy the entire column length, it would be treated as an error.

Following shows an example of specifying NULL characters at column level.

Note that when specifying NULL character at column level we should match the length of the column width.

<> /home/sukul1 $ cat employeedet3.dat

rahul,dravID       ***2010-01-01100000

SAURAV,GANGULY     ***2010-10-01200000

sukul1=> COPY USER_30_DAY_TABLES.EMPLOYEE_1

sukul1-> (

sukul1(> NAME,

sukul1(> AGE NULL '***',

sukul1(> JOINING_DATE FILLER VARCHAR(10),

sukul1(> JOINING_YEAR AS TO_NUMBER(TO_CHAR(TO_DATE(JOINING_DATE,'YYYY-MM-DD'),'YYYY')),

sukul1(> SALARY

sukul1(> )

sukul1-> FROM LOCAL '/home/sukul1/employeedet3.dat'

sukul1-> FIXEDWIDTH COLSIZES(19,3,10,6)

sukul1-> DIRECT;

 Rows Loaded

-------------

           2

(1 row)

sukul1=> select * FROM USER_30_DAY_TABLES.EMPLOYEE_1;

        NAME         | AGE | JOINING_YEAR | SALARY

---------------------+-----+--------------+--------

 SAURAV,GANGULY      |     |         2010 | 200000

 rahul,dravID        |     |         2010 | 100000

(2 rows)