Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

decode Bug?

Aketi JyuuzouJun 12 2006 — edited Jun 15 2006
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE	10.1.0.2.0	Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> select decode(Col1,null,Col2,Col1) as Col3,
  2  case when decode(Col1,null,Col2,Col1) >= TO_DATE('2006-08-01', 'YYYY-MM-DD')
  3       then 1 else 0 end as "whereIsTrue"
  4  from (select TO_DATE('2006-08-12', 'YYYY-MM-DD') as Col1,null as Col2 from dual
  5        union select null,TO_DATE('2006-08-13', 'YYYY-MM-DD') from dual
  6        union select null,TO_DATE('2006-08-14', 'YYYY-MM-DD') from dual)
  7  ;

COL3      whereIsTrue
--------  -----------
06-08-12            1
06-08-13            1
06-08-14            1

SQL> select decode(Col1,null,Col2,Col1) as Col3,
  2  case when decode(Col1,null,Col2,Col1) >= TO_DATE('2006-08-01', 'YYYY-MM-DD')
  3       then 1 else 0 end as "whereIsTrue"
  4  from (select TO_DATE('2006-08-12', 'YYYY-MM-DD') as Col1,null as Col2 from dual
  5        union select null,TO_DATE('2006-08-13', 'YYYY-MM-DD') from dual
  6        union select null,TO_DATE('2006-08-14', 'YYYY-MM-DD') from dual)
  7  WHERE decode(Col1,null,Col2,Col1) >= TO_DATE('2006-08-01', 'YYYY-MM-DD')
  8  ;

COL3      whereIsTrue
--------  -----------
06-08-12            1

Why one recode?

Comments

474126
It's strange...

The test in 10Gr2 is below,
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select decode(Col1,null,Col2,Col1) as Col3,
  2    case when decode(Col1,null,Col2,Col1) >= TO_DATE('2006-08-01', 'YYYY-MM-D
D')
  3    then 1 else 0
  4    end as "whereIsTrue"
  5  from (select TO_DATE('2006-08-12', 'YYYY-MM-DD') as Col1,null as Col2 from
dual
  6        union select null,TO_DATE('2006-08-13', 'YYYY-MM-DD') from dual
  7        union select null,TO_DATE('2006-08-14', 'YYYY-MM-DD') from dual)
  8  /

COL3      whereIsTrue
--------- -----------
12-AUG-06           1
13-AUG-06           1
14-AUG-06           1

SQL> select decode(Col1,null,Col2,Col1) as Col3,
  2    case when decode(Col1,null,Col2,Col1) >= TO_DATE('2006-08-01', 'YYYY-MM-D
D')
  3    then 1 else 0
  4    end as "whereIsTrue"
  5  from (select TO_DATE('2006-08-12', 'YYYY-MM-DD') as Col1,null as Col2 from
dual
  6        union select null,TO_DATE('2006-08-13', 'YYYY-MM-DD') from dual
  7        union select null,TO_DATE('2006-08-14', 'YYYY-MM-DD') from dual)
  8    WHERE decode(Col1,null,Col2,Col1) >= TO_DATE('2006-08-01', 'YYYY-MM-DD')
  9  /

COL3      whereIsTrue
--------- -----------
12-AUG-06           1
13-AUG-06           1
14-AUG-06           1

SQL>
Nicolas Gasparotto

Hi,

Maybe bug #3678133 - fixed in 10.1.0.4 (Server Patch Set).
What happens if you replace

WHERE decode(Col1,null,Col2,Col1) >= TO_DATE('2006-08-01', 'YYYY-MM-DD')

by

WHERE nvl(Col1,Col2) >= TO_DATE('2006-08-01', 'YYYY-MM-DD')

?

A workaround consist into Set event 10339 level 1.

Nicolas.

Aketi Jyuuzou

I would use nvl.
works fine.
thank you.

SQL> select nvl(Col1,Col2) as Col3,
  2  case when nvl(Col1,Col2) >= TO_DATE('2006-08-01', 'YYYY-MM-DD')
  3       then 1 else 0 end as "whereIsTrue"
  4  from (select TO_DATE('2006-08-12', 'YYYY-MM-DD') as Col1,cast(null as date) as Col2 from dual
  5        union select cast(null as date),TO_DATE('2006-08-13', 'YYYY-MM-DD') from dual
  6        union select cast(null as date),TO_DATE('2006-08-14', 'YYYY-MM-DD') from dual)
  7  WHERE nvl(Col1,Col2) >= TO_DATE('2006-08-01', 'YYYY-MM-DD')
  8  ;

COL3      whereIsTrue
--------  -----------
06-08-12            1
06-08-13            1
06-08-14            1
507047
HI Gasparotto,

is it the bug in 10.1.0.4 or it exist in Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production also?

But see following result:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production



1 select decode(Col1,null,Col2,Col1) as Col3,
2 case when decode(Col1,null,Col2,Col1) >= TO_DATE('2006-08-01', 'YYYY-MM-DD')
3 then 1 else 0 end as "whereIsTrue"
4 from
5 (select TO_DATE('2006-08-12', 'YYYY-MM-DD') as Col1,null as Col2 from dual
6 union select null,TO_DATE('2006-08-13', 'YYYY-MM-DD') from dual
7* union select null,TO_DATE('2006-08-14', 'YYYY-MM-DD') from dual)
SQL> /

COL3 whereIsTrue
--------- -----------
12-AUG-06 1
13-AUG-06 1
14-AUG-06 1

SQL> ed
Wrote file afiedt.buf

1 select decode(Col1,null,Col2,Col1) as Col3,
2 case when decode(Col1,null,Col2,Col1) >= TO_DATE('2006-08-01', 'YYYY-MM-DD')
3 then 1 else 0 end as "whereIsTrue"
4 from
5 (select TO_DATE('2006-08-12', 'YYYY-MM-DD') as Col1,null as Col2 from dual
6 union select null,TO_DATE('2006-08-13', 'YYYY-MM-DD') from dual
7 union select null,TO_DATE('2006-08-14', 'YYYY-MM-DD') from dual)
8* WHERE decode(Col1,null,Col2,Col1) >= TO_DATE('2006-08-01', 'YYYY-MM-DD')
SQL> /

COL3 whereIsTrue
--------- -----------
12-AUG-06 1
13-AUG-06 1
14-AUG-06 1



so I believe this could be the bug in Only 10g first release.

Cheers
Ram Kanala
Nicolas Gasparotto
is it the bug in 10.1.0.4 or it exist in Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production also?
This bug was introduced in 10.1.0.2

Nicolas.
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 13 2006
Added on Jun 12 2006
5 comments
2,061 views