| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: WinNT / 8.0.5 / DECODE function affecting Century result in d
!! 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
*
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
elkinsl_at_flash.net
214.954.1781
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]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: elkinsl_at_flash.net 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: ListGuru_at_fatcity.com (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).Received on Thu Sep 13 2001 - 13:04:01 CDT
 |
 |