OraFAQ Forum: Server Utilities » SQL Loader function and nullif issue

Home » RDBMS Server » Server Utilities » SQL Loader function and nullif issue (9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader function and nullif issue [message #364682]

Tue, 09 December 2008 10:06

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

Report message to a moderator

Re: SQL Loader function and nullif issue [message #364684 is a reply to message #364682]

Tue, 09 December 2008 10:24

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SQL Loader function and nullif issue [message #364685 is a reply to message #364684]

Tue, 09 December 2008 10:31

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.

Report message to a moderator

Re: SQL Loader function and nullif issue [message #364686 is a reply to message #364685]

Tue, 09 December 2008 10:33

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SQL Loader function and nullif issue [message #364688 is a reply to message #364686]

Tue, 09 December 2008 10:47

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.

Report message to a moderator

Re: SQL Loader function and nullif issue [message #364690 is a reply to message #364688]

Tue, 09 December 2008 11:05

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SQL Loader function and nullif issue [message #364693 is a reply to message #364690]

Tue, 09 December 2008 11:32

Barbara Boehmer
Messages: 9101
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'))",

Report message to a moderator

Re: SQL Loader function and nullif issue [message #364878 is a reply to message #364693]

Wed, 10 December 2008 05:53

clmurphy
Messages: 4
Registered: December 2008

Junior Member

thank you, that worked

Report message to a moderator

Previous Topic:	SQL Loader via Concurrent Program...response?
Next Topic:	IMP-00010: not a valid export file, header failed verification

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 19:28:04 CST 2024