SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table fullJoinTest1(col1 char(14));
Table created.
SQL> create table fullJoinTest2(col1 char(14));
Table created.
SQL> insert all
2 into fullJoinTest1 values('aaa')
3 into fullJoinTest1 values('bbb')
4 into fullJoinTest2 values('aaa')
5 into fullJoinTest2 values('ccc')
6 select 1 from dual;
4 rows created.
SQL> select nvl(a.col1,b.col1) as aaaa
2 from fullJoinTest1 a full join fullJoinTest2 b
3 on a.col1=b.col1
4 where nvl(a.col1,b.col1)= 'ccc';
no rows selected
SQL> create table fullJoinTest3 as
2 select nvl(a.col1,b.col1) as "WhatIsType?"
3 from fullJoinTest1 a full join fullJoinTest2 b
4 on a.col1=b.col1
5 where nvl(a.col1,b.col1)= 'ccc';
Table created.
SQL> desc fullJoinTest3
Name Null? Type
------------- -------- ------------
WhatIsType? VARCHAR2(14)
SQL> create table fullJoinTest4 as
2 select case when a.col1 is not null then a.col1 else b.col1 end as "WhatIsType?"
3 from fullJoinTest1 a full join fullJoinTest2 b
4 on a.col1=b.col1;
Table created.
SQL> desc fullJoinTest4
Name Null? Type
----------- -------- --------
WhatIsType? CHAR(14)
My question is why
nvl function changes dataType from char to varchar2 ?
Even
case expression does not change dataType.
I insist that this is a
nvl functions bug is not it.