!! Please do not post Off Topic to this List !!
George,
The behavior is explained by the nature of the DECODE statement. The data
type of *all* the return arguments is defined by the datatype of the *first*
return argument. In your case, NULL is the first return argument, and, the
DECODE treats this as a VARCHAR2 datatype. As a result, the value being
returned by the second argument is defined as a VARCHAR2 datatype. Here is a
comment from the docs on the DECODE statement. Note the comment concerning
how nulls are treated:
"Oracle automatically converts the return value to the same datatype as the
first result. If the first result has the datatype CHAR or if the first
result is null, then Oracle converts the return value to the datatype
VARCHAR2."
Here is the "old" one giving you problems:
DECODE(:bdate,NULL,NULL,TO_DATE('19'||:bdate,'YYYYMMDD')
Even though the last argument was a DATE due to your TO_DATE, it was
*implicitly* converted to a VARCHAR2 string to match the datatype, VARCHAR2,
of the first return argument, NULL. More than likely your NLS_DATE_FORMAT
was something like DD-MON-YY. So, something like "19620629" would get
converted to "62-JUN-29". This would then get converted back to a DATE when
inserted into the column in the table. And since the century significance
had been lost, the *current* century would be assumed.
Here is an example of how the first return type defines the return types of
all arguments:
SQL> select decode(ename,null,0,ename) from emp;
select decode(ename,null,0,ename) from emp
*
ERROR at line 1:
ORA-01722: invalid number
Notice that my first return argument is 0, a number. As a result, it tries
to convert ENAME to a number when it returns it. Thus the error.
If I remember correctly, someone posted a response where they did a
TO_DATE(null) for the first return argument and the problem was fixed (since
the TO_DATE of the last argument would remain a DATE and wouldn't do an
implicit conversion to a VARCHAR2 string and back to a date when going into
the table). They mentioned that for whatever reason it appeared to be return
a STRING value and thus the use of the TO_DATE. Well, the above explains
why.
This "gotcha" was documented in one of their Y2K papers (had to watch out
for NLV functions also). I saw it bite a couple of people on the tail -- the
implicit conversion to a VARCHAR2 and back wasn't all that obvious unless
someone knew the details of the decode, or, ran into the problem.
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of George
> Hofilena
> Sent: Thursday, September 13, 2001 12:50 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: WinNT / 8.0.5 / DECODE function affecting Century result in
> d
>
>
> !! Please do not post Off Topic to this List !!
>
> Thanks for all the responses. I just switched the operators like this.
> Seems to work.
>
> TO_DATE(DECODE(:bdate,NULL,NULL,'19'||:bdate),'YYYYMMDD')
>
> This is legacy data so the '19' prefix is only temporary. RR was
> one of the
> first that I tried. Didn't work.
> Anyway, I guess I was just curious as to why Oracle changed the century in
> such a manner when I introduce the DECODE the way I originally did.
>
> This is no longer urgent in my case but I'd still be interested to know if
> somebody has any input as to why Oracle behaves the way it does.
>
> Thanks,
>
> George
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
