Home » RDBMS Server » Server Utilities » Date checking
Date checking [message #221594] Tue, 27 February 2007 10:09 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member
Hi,

I have a control file which loads the data into a temporary table. While inserting the rows into the temporary table, we'll need to validate a date field so that the invoice_date shouldn't be null and also the invoice_date should be greater than '01-Jan-1970'. If the date is less than 01-Jan-1970, then a default value '01-Jan-2006' should be substituted for the invoice date column.

I was able to check for the null values for the invoice_date but dont know how to check if the date < 1970 and if so, subsitute a default value.

Table Name : nfc_int_ap_data
Columns :
file_id Number(4)
valid_flag Char(1)
invoice_num Number(10)
Invoice_Date Date
Currency_Code Varchar(10)

The Control file :

LOAD DATA
APPEND
INTO TABLE nfc_int_ap_data
APPEND
WHEN (001:001) = 'D'
TRAILING NULLCOLS
(
file_id "nfc_int_data_files_seq.currval"
,valid_flag CONSTANT 'Y'
,invoice_num POSITION(002:051) CHAR
,invoice_date POSITION(113:120) DATE 'YYYYMMDD' NULLIF invoice_date=BLANKS
,currency_code POSITION(121:135) CHAR
)

Please let me know how to accomplish this??

Thanks
Safeeq

Report message to a moderator

Re: Date checking [message #221638 is a reply to message #221594] Tue, 27 February 2007 19:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> dont know how to check if the date < 1970 and if so, subsitute a default value.
Do it with a single UPDATE statement after the table is loaded.

Report message to a moderator

Re: Date checking [message #221750 is a reply to message #221638] Wed, 28 February 2007 05:17 Go to previous messageGo to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member
Hi,

Found a way to subsitute a default value using decode statement but unfortunately sql loader is erroring out.

LOAD DATA
APPEND
INTO TABLE nfc_int_ap_data
APPEND
WHEN (001:001) = 'D'
TRAILING NULLCOLS
(
file_id "nfc_int_data_files_seq.currval"
,valid_flag CONSTANT 'Y'
,invoice_num POSITION(002:051) CHAR
,invoice_date POSITION(113:120) DATE 'YYYYMMDD' " DECODE(SIGN(TO_CHAR(:invoice_date,'YYYYMMDD')-TO_CHAR(TO_DATE('19700101','YYYYMMDD'),'YYYYMMDD')),-1,'99991301',1,:invoice_date,0,:in voice_date,NULL) ",currency_code POSITION(121:135) CHAR
)


Not sure, if this decode & sign will help me or not. Just doing some workaround. Please let me know if someone have found a better solution.

thanks
Safeeq

Report message to a moderator

Re: Date checking [message #221777 is a reply to message #221750] Wed, 28 February 2007 06:39 Go to previous message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member
Hi,

got the solution and it works fine for me.

LOAD DATA
APPEND
INTO TABLE nfc_int_ap_data
APPEND
WHEN (001:001) = 'D'
TRAILING NULLCOLS
(
file_id "nfc_int_data_files_seq.currval"
,valid_flag CONSTANT 'Y'
,invoice_num POSITION(002:051) CHAR
,invoice_date POSITION(113:120) DATE 'YYYYMMDD' NULLIF invoice_date=BLANKS "DECODE(SIGN(:invoice_date-'19700101'),1,:invoice_date,-1,'20070101')",currency_code POSITION(121:135) CHAR
)

Thanks
Safeeq

Report message to a moderator

Previous Topic: Can we validate the exported DMP file? (merged)
Next Topic: Database Language
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jul 03 09:56:54 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.