Decode inside insert statement

[hench]

Hi everyone

I have tried the following SQL under the Scott/Tiger schema :

insert into emp(empno, hiredate)
values(9999, decode(1, 0, NULL, to_date('18-09-2003 18:30:00', 'DD-MM-YYYY HH24:MI:SS')));

Next, I do the following :

select empno, to_char(hiredate, 'DD-MM-YYYY HH24:MI:SS') from emp where empno = 9999;

But in this output the time portion of the hiredate-field is now 00:00:00

Why is the time portion truncated ?

Thanks
Hench

 

[SantaMufasa]

Hench,

This is a bit puzzling since if you swap your arguments around in the decode statement, it produces proper results:

insert into emp3(empno, hiredate)
values(9999, decode(1, 0, NULL, to_date('18-09-2003 18:30:00', 'DD-MM-YYYY HH24:MI:SS')));

insert into emp3(empno, hiredate)
values(9999, decode(1,1,to_date('18-09-2003 18:30:00', 'DD-MM-YYYY HH24:MI:SS'),null));

select empno, to_char(hiredate, 'DD-MM-YYYY HH24:MI:SS') from emp3;

EMPNO TO_CHAR(HIREDATE,'D
---------- -------------------
9999 18-09-2003 00:00:00
9999 18-09-2003 18:30:00

Hmmmm...

 

[sem]

I may assume the following.

In the first statement, as the first choice in DECODE is untyped NULL, it's treated as CHAR; then, DATE is casted to the same type and, as no mask for backward conversion from date to char is applied, is converted by default mask. The correct statement should be:

insert into emp3(empno, hiredate)
values(9999, decode(1, 0, to_date(NULL), to_date('18-09-2003 18:30:00', 'DD-MM-YYYY HH24:MI:SS')));



Regards, Dima

 

[dsanchez2]

The documentation for Decode saids:

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.

So sem's solution should resolve your problem.

 

[hench]

Hi again !

Thanks a lot.

Now it works fine, just what I needed to know...

Regards
Hench