Re: NULL values in date fields - How ?

From: Jurij Modic <jmodic_at_src.si>
Date: 1998/11/27
Message-ID: <365f2aeb.679434@news.arnes.si>#1/1

On Fri, 27 Nov 1998 15:32:50 +0100, "Jørgen Haukland" <jorgen.hauikland_at_fou.telenor.no> wrote:

>Hello !
>
>I'm working on an application where we use SQL*Loader to get data from an
>IDMS dB(via csv ascii-files) in an Oracle 8.0.4 (NT) base. The problem is
>that empty date fields in my acii files are stored as '00000' (initially a
>kind of ddmmyy-format).
>
>Oracle returns error when I try to to load '00000' in a date field. I have
>ended up declaring these field VARCHAR2, but I don't like this solution.
>
>Here's my .ctl-file:
>
> LOAD DATA
> INFILE 'M:\testdat.dat'
> TRUNCATE
> INTO TABLE "TEST_DATE"
> FIELDS TERMINATED BY ':'
> (date NULLIF date = "000000" "TO_DATE(:date,'DD:MM:YY')")
>
> My ascii-file look this way:
>
> 000000:
> 020496:
> 091247:
> 000000:
> 280258:
> 010101:
> 000000:
> 191297:
>
>I get error messages on post 1, 4 and 7.
>
>Any suggestions ??

(First of all, I belive there are some typing errors in your example: - I belive your date format mask should be 'DDMMYY', not 'DD:MM:YY' - I don't think DATE is a valid column name as it is reserved word for data type. I use column name DATE_FLD instead in the following examples)

I belive you have problems because of the leeding whitespaces in your data file. If there realy exist leading blanks in front of your dates (as it looks from your message), then loaders takes the field DATE_FLD *with* leading spaces, and so the [NULLIF date_fld = "000000"] is never evaluated to true, because the field actualy begins whit blank, not with zero. For example, if there is a single blank before your dates, your problems will be solved with the following nullif clause (note the single blank before the first zero!):

    FIELDS TERMINATED BY ':'
      (date_fld DATE "DDMMYY" NULLIF date_fld = " 000000"

If the number of leading blanks is not fixed, you could use a LTRIM function in an SQL string, like the following:

    FIELDS TERMINATED BY ':'

      (date_fld "TO_DATE(DECODE(LTRIM(:date_fld), '000000', NULL,
                         :date_fld), 'DDMMYY')")

Or alternatively, you can include a "dummy" OPTIONALY ENCLOSED BY clause in your control file - this cause implicit triming of leading whitespaces:

    FIELDS TERMINATED BY ':' OPTIONALLY ENCLOSED BY '#'       (date_fld DATE "DDMMYY" NULLIF date_fld = "000000"

I succesfuly loaded your sample data using all three methods.

>-jh
>NORWAY
HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Nov 27 1998 - 00:00:00 CST