Content-class: urn:content-classes:message
MIME-Version: 1.0
Subject: RE: sql loader question
Date: Mon, 17 Dec 2007 13:42:27 -0500
Message-ID: <72AB68B424526641A8514835270C2EB502D0C7CF@EXM-OMF-21.Ceg.Corp.Net>
In-Reply-To: <121720071713.26183.4766AE410004A047000066472200734840079D9A00000E09A1020E979D@comcast.net>
Thread-Topic: sql loader question
References: <121720071713.26183.4766AE410004A047000066472200734840079D9A00000E09A1020E979D@comcast.net>
From: "Brady, Mark" <Mark.Brady@Constellation.Com>
To: <ryan_gaffuri@comcast.net>,
 <oracle-l@freelists.org>
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: Mark.Brady@Constellation.Com

Maybe this isn't a direct answer to your question but you might try
using External Tables and/or NVL2. External Tables will give you a much
more comfortable and familiar SQL interface to your data. Also, you
should look at NVL2. It gives you the ability to specify what to do in
the event that your test is not null.

-----Original Message-----
From: oracle-l-bounce@freelists.org
[mailto:oracle-l-bounce@freelists.org] On Behalf Of
ryan_gaffuri@comcast.net
Sent: Monday, December 17, 2007 12:14 PM
To: oracle-l@freelists.org
Subject: sql loader question

I rarely use sql loader. 

I need to account for nulls and set them to January 1, 9999

I am getting errors when I try to use a to_date function in sql loader
even though I have seen examples just like this on the web. This is 10.2

my_date date "to_date(:my_date,'YYYY-MM-DD')"

I get: ORA-01821: date format not recognized

The following works:

my_date date "YYYY-MM-DD"

I am trying to get to something like this:

my_date date
"decode(:my_date,null,to_date('9999-01-01','YYYY-MM-DD'),to_date(:my_dat
e,'YYYY-MM-DD')"

I dont think nvl will give me what i want since I still need an "else"
mask. At this point, I can't get the basic syntax right. 

Here is the header part of the control file

LOAD DATA
CHARACTERSET UTF8
INFILE "load.data" "var 7"
APPEND INTO TABLE my_table FIELDS TERMINATED BY ' ' ENCLOSED By "'" AND
"'"

>>> This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the
addressee.  If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2

--
http://www.freelists.org/webpage/oracle-l