Home » RDBMS Server » Server Utilities » SQLLDR with Date column
SQLLDR with Date column [message #195296] Wed, 27 September 2006 16:51 Go to next message
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 Go to previous messageGo to next message
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)
)
Re: SQLLDR with Date column [message #195441 is a reply to message #195305] Thu, 28 September 2006 11:36 Go to previous message
vqd2697
Messages: 36
Registered: October 2004
Member
Thanks, it's working now.
Previous Topic: expdp -export error when using data pump- need help
Next Topic: import/export
Goto Forum:
  


Current Time: Fri Dec 09 19:15:22 CST 2016

Total time taken to generate the page: 0.08910 seconds