| SQLLDR with Date column [message #195296] |
Wed, 27 September 2006 16:51  |
vqd2697
Messages: 36
Registered: October 2004
|
Member |
|
|
I have control file with date (YYYYMMDD) column which some rows is NULL and some rows is 0 (integer)in the data. How do I specific on control file so if they see a 0 and load as NULL? Because everytime they SQLLDR see 0 then that records is not able to load due to format YYYYMMDD
LOAD DATA APPEND INTO TABLE TEST
TRAILING NULLCOLS
(
CRR_IDNM POSITION(1:5) ,
SYS_ID POSITION(6:10) ,
RX_IDNM POSITION(11:40) ,
RX_LNNUM POSITION(41:46) ,
RX_SEQ POSITION(47:49) ,
RX_WRITE DATE "YYYYMMDD",
RX_SUBMIT DATE "YYYYMMDD"
)
Thanks,
Vu
|
|
|
|
| Re: SQLLDR with Date column [message #195305 is a reply to message #195296] |
Wed, 27 September 2006 19:06  |
Nirmala
Messages: 43
Registered: October 2004
|
Member |
|
|
Try modifying you sql as given below.
LOAD DATA APPEND INTO TABLE TEST
TRAILING NULLCOLS
(
CRR_IDNM POSITION(1:5) ,
SYS_ID POSITION(6:10) ,
RX_IDNM POSITION(11:40) ,
RX_LNNUM POSITION(41:46) ,
RX_SEQ POSITION(47:49) ,
RX_WRITE DATE "YYYYMMDD" "DECODE(:RX_WRITE ,0,'',:RX_WRITE),
RX_SUBMIT DATE "YYYYMMDD" "DECODE(:RX_SUBMIT ,0,'',:RX_SUBMIT)
)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
