Re: Possible Y2K trap in using DECODE?

From: Jurij Modic <jmodic_at_src.si>
Date: Sun, 10 Jan 1999 22:40:10 GMT
Message-ID: <3699203e.42677404@news.siol.net>

On Sun, 10 Jan 1999 03:04:08 +0100, Erwin Dondorp <erwindon_at_wxs.nl> wrote:

>Jurij,
>
>Thanks for telling us about this subject (and explaining it!)
>I think you can solve your problem by recoding your statement to:
>
>INSERT INTO destination_table (date_column)
> SELECT DECODE(char_date, '00000000', TO_DATE(NULL), '01010001', NULL,
> TO_DATE(char_date,'DDMMYYYY'))
> FROM temp_table;
>
>The only thing that has changed is the TO_DATE on the first result in the
>DECODE.
>This is possible because even the NULL value has a type.

Erwin,

Thanks for the suggestion. Unfortunatelly this doesn't help (btw, this was the first thing I tried when discovered this behaviour) - although after this modification the first NULL result is of type DATE, the resulted value is still NULL and DECODE will transform the result to VARCHAR2 type regardless of the type of the first NULL result!

The only workaround for this problem I could found in the meantime was to force the first result in the DECODE to be non-null date value and to make sure this first condition never evaluates to TRUE. In my case for example I know that in a source file all date values can contain only numerical characters, so I can use the following DECODE to be sure the resulted datatype will be of type DATE:

INSERT INTO destination_table (date_column)

  SELECT DECODE(char_date, 'xyzabc_-', SYSDATE,
                           '00000000', NULL,
                           '01010001', NULL,
                TO_DATE(char_date,'DDMMYYYY'))

  FROM temp_table;

Because I know the char_date will never be of value 'xyzabc_-' I can be sure the DECODE function will never return the SYSDATE, and because the SYSDATE can't be NULL there will be no implicit char-to-date conversion inside the DECODE.

> Erwin Dondorp
> http://www.wxs.nl/~erwindon

Regards,

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 Sun Jan 10 1999 - 16:40:10 CST