| SQL Loader -- Convert invalid dates to null? [message #71313] |
Mon, 14 October 2002 13:20  |
Scot
Messages: 7
Registered: August 2002
|
Junior Member |
|
|
How do I convert an invalid date to a null? I have used the nullif clause if a field has a known value, such as:
cs03mbrs_dte_last_updt POSITION(25:32) DATE 'YYYYMMDD'
NULLIF cs03mbrs_dte_last_updt = '00000000',
And this works fine for most cases. But what if there is an invalid date in the field, such as feb 29 of a year that isn't a leap year, like '19940229' ? Is there some sort of IS_DATE function I can use in a control file?
I'm using SQL*Loader: Release 8.1.6.2.0 on Tru64 Unix.
Thanks.
|
|
|
|
|
|
| Re: SQL Loader -- Convert invalid dates to null? [message #71319 is a reply to message #71317] |
Tue, 15 October 2002 07:04  |
Scot
Messages: 7
Registered: August 2002
|
Junior Member |
|
|
Thanks for the suggestion, but how can I use the TO_DATE function in the control file? I have only been able to find the simplest of examples with the NULLIF clause, things like comparing if one value is exactly equal to another. Do you have any more complex examples?
How would I apply a function call? I tried:
cs03chld_dte_chld_emanc POSITION(24:31) DATE 'YYYYMMDD'
NULLIF NOT TO_DATE(cs03chld_dte_chld_emanc),
but got a syntax error. Also tried it with !TO_DATE() but that didn't work either.
|
|
|
|
| Re: SQL Loader -- Convert invalid dates to null? [message #71321 is a reply to message #71317] |
Tue, 15 October 2002 12:47 |
Scot
Messages: 7
Registered: August 2002
|
Junior Member |
|
|
Hmm. Thanks for the suggestion, but I apparently can't use SQL Expressions because I'm using Oracle 8i and using a direct path load. Apparently only 9i allows direct path to use expressions.
Also, I found out my problems with the NULLIF clause, and why I could only find simple examples of expressions -- only simple (comparing a field to a constant value) expressions are allowed.
Here is the syntax diagram:
fld_cond ::=
[[(]] {full_fieldname | pos_spec} operator {'char_string' | X'hex_string' | BLANKS} [[)]] AND
Looks like I'm out of luck. Thanks again for your help.
|
|
|
|
| Re: SQL Loader -- Convert invalid dates to null? [message #71328 is a reply to message #71317] |
Wed, 16 October 2002 07:29 |
Scot
Messages: 7
Registered: August 2002
|
Junior Member |
|
|
Well, I've considered paying the performance hit and doing the load conventional, at least until we goto 9i. So, I tried the Decode solution, but I couldn't get it to work because no matter what I did, if I passed it an invalid date, it gave me an error.
But, I liked your approach on creating a function, and surpressing the error. So I took your function and modified it slightly to create a new to_date, one that returns null if the string passed is not a valid date.
Here it is:
CREATE OR REPLACE FUNCTION cse_to_date(d CHAR, f CHAR)
RETURN DATE
IS
d_date DATE;
BEGIN
d_date := TO_DATE(d, f);
RETURN d_date;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END cse_to_date;
/
Thanks again for your help.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
