Re: sql loader question
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: Mark.Brady@xxxxxxxxxxxxxxxxx
- Date: Mon, 17 Dec 2007 12:01:30 -0800
decode() of course still works for this.
Makes me wonder what the purpose is of nvl2().
Perhaps decode() is on the way out? It still appears in the 11g docs.
IIAC, did you get this to work Ryan?
On Dec 17, 2007 10:42 AM, Brady, Mark <Mark.Brady@xxxxxxxxxxxxxxxxx> wrote:
> 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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
> ryan_gaffuri@xxxxxxxxxxx
> Sent: Monday, December 17, 2007 12:14 PM
> To: oracle-l@xxxxxxxxxxxxx
> 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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
//www.freelists.org/webpage/oracle-l
Other related posts:
