Home » RDBMS Server » Server Utilities » SQL Loader function and nullif issue (9i)
SQL Loader function and nullif issue [message #364682] Tue, 09 December 2008 10:06 Go to next message
clmurphy
Messages: 4
Registered: December 2008
Junior Member
I am trying to load a date field that is mm/dd/yyyy 0:00:00, I need to truncate the time element and also account for the field being blank.

this is the function in the control file but it is not working

"trunc(to_date(:for_dt,'MM/DD/YYYY HH24:MI:SS')) NULLIF for_dt=BLANKS",

Can NULLIF not be used with a function?

Thanks
Re: SQL Loader function and nullif issue [message #364684 is a reply to message #364682] Tue, 09 December 2008 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know but you don't need to truncate the date if it is always "0:00:0" in your file as trunc is just putting this time along with the date.

Regards
Michel
Re: SQL Loader function and nullif issue [message #364685 is a reply to message #364684] Tue, 09 December 2008 10:31 Go to previous messageGo to next message
clmurphy
Messages: 4
Registered: December 2008
Junior Member
we could not get the date to load with the time element and we did not need it anyway so I was trying to remove it from the file. Is there a specific data type that sqlloader will recognize, I tried timestamp but I received a loader error.
Re: SQL Loader function and nullif issue [message #364686 is a reply to message #364685] Tue, 09 December 2008 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I don't understand, do you have a real time or 0:00:00 in your file?
What is the format of your file?
Could you post control file and a line of data file?

Regards
Michel
Re: SQL Loader function and nullif issue [message #364688 is a reply to message #364686] Tue, 09 December 2008 10:47 Go to previous messageGo to next message
clmurphy
Messages: 4
Registered: December 2008
Junior Member
LOAD DATA
APPEND
INTO TABLE xxxxx.xxx_xxxxxx_data
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(fileNo,
file_pos_orig,
file_owner,
file_type,
file_type_orig,
file_dt "trunc(to_date(:file_dt,'MM/DD/YYYY HH24:MI:SS')) NULLIF :file_dt=BLANKS",
file_line_amt)

Data:

9999999999,1,2,2,2,2,8/28/1995 0:00:00,,

some of the date fields are empty so they will just show as ,, like the last field in the data.
Re: SQL Loader function and nullif issue [message #364690 is a reply to message #364688] Tue, 09 December 2008 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
file_dt "to_date(:file_dt,'MM/DD/YYYY HH24:MI:SS')" NULLIF (:file_dt=BLANKS),

or the opposite, I don't know if order matters:
file_dt NULLIF (:file_dt=BLANKS) "to_date(:file_dt,'MM/DD/YYYY HH24:MI:SS')",

Regards
Michel
Re: SQL Loader function and nullif issue [message #364693 is a reply to message #364690] Tue, 09 December 2008 11:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Order matters and don't use : with NULLIF:

file_dt NULLIF file_dt=BLANKS "trunc(to_date(:file_dt,'MM/DD/YYYY HH24:MI:SS'))",
Re: SQL Loader function and nullif issue [message #364878 is a reply to message #364693] Wed, 10 December 2008 05:53 Go to previous message
clmurphy
Messages: 4
Registered: December 2008
Junior Member
thank you, that worked
Previous Topic: SQL Loader via Concurrent Program...response?
Next Topic: IMP-00010: not a valid export file, header failed verification
Goto Forum:
  


Current Time: Wed Dec 07 08:54:21 CST 2016

Total time taken to generate the page: 0.14474 seconds