Problems with decode function in Oracle ... Turning me mad

[Medhatithi]

Hi,
I am facing a strange problem with decode function in oracle. My table name is status_hist. Below is the query I am hitting on this table:

select max(decode(void ed_flag,'Y',nul l,decode(status _hist.status,'W D',status_date) ))
from status_hist
where sk_seq=6574

The result returned is '29-SEP-05'

However, I checked out the table status_hist to see that for sk_seq=6574 and voided_flag not equals to 'Y' and status='WD', the maximum status_date is '12/22/2005 10:28:29 AM' . However, if I use the CASE-WHEN function, then I get accurate results.

Also, when I hit the query

select max(to_date(dec ode(voided_flag ,'Y',null,decod e(status_hist.s tatus,'WD',stat us_date)),'dd-mon-yy'))
from status_hist
where sk_seq=6574

the result is '12/22/2005'

Can anyone please give an explanation to this?

[Dave44]

So, in going back to the original question.

the difference in the results from these two queries:

select max(decode(voided_flag,'Y',null,decode(status_hist.status,'WD',status_date)))
from status_hist 
where sk_seq=6574
 
 
select max(to_date(decode(voided_flag,'Y',null,decode(status_hist.status,'WD',status_date)),'dd-mon-yy'))
from status_hist 
where sk_seq=6574
 
 

is because max on a string is going to return different results than max on a date. and max on the same to_char(date_fi eld) will be entirely dependant on the format it is output in.

[143]dave44@ORADB> create table temp (dat   date);
 
Table created.
 
Elapsed: 00:00:02.53
[143]dave44@ORADB> 
[143]dave44@ORADB> insert into temp values (sysdate);
 
1 row created.
 
Elapsed: 00:00:00.70
[143]dave44@ORADB> 
[143]dave44@ORADB> insert into temp values (sysdate-10);
 
1 row created.
 
Elapsed: 00:00:00.53
[143]dave44@ORADB> 
[143]dave44@ORADB> commit;
 
Commit complete.
 
Elapsed: 00:00:00.57
[143]dave44@ORADB> 
[143]dave44@ORADB> select max(dat) from temp;
 
MAX(DAT)
---------
08-MAR-07
 
Elapsed: 00:00:00.92
[143]dave44@ORADB> 
[143]dave44@ORADB> select max(to_char(dat,'dd/mm/yyyy'))
  2  from temp;
 
MAX(TO_CHA
----------
26/02/2007
 
Elapsed: 00:00:00.65
[143]dave44@ORADB> select max(to_char(dat,'mm/dd/yyyy'))
  2  from temp;
 
MAX(TO_CHA
----------
03/08/2007
 

[Medhatithi]

I wasnt trying to return the exact query back as much as demonstrate my point. decode is a function that was created a long time ago. I double checked, it does return the datatype of the first data type possibility. so because the default date out has no time values that data is lost. so the to_date re created the string back to a date but the time portion didnt exist and therefore couldnt be created.

the case when code was created as an evolution of the decode. they werent created to be equal, case-when is a more powerful tool. but it is bound by having the same datatype in all possibilities.

you never did show me the case when statement. are you sure that logically they were identicle? if i could see it, then i could let you know.

Yeah, they are the same logically. I checked it out.

select max(case when voided_flag='Y' then null
else (case when status_hist.sta tus='WD' then status_date else null end)end) from status_hist
where sk_seq=706379

[Medhatithi]

So, in going back to the original question.

the difference in the results from these two queries:

select max(decode(voided_flag,'Y',null,decode(status_hist.status,'WD',status_date)))
from status_hist 
where sk_seq=6574
 
 
select max(to_date(decode(voided_flag,'Y',null,decode(status_hist.status,'WD',status_date)),'dd-mon-yy'))
from status_hist 
where sk_seq=6574
 
 

is because max on a string is going to return different results than max on a date. and max on the same to_char(date_fi eld) will be entirely dependant on the format it is output in.

[143]dave44@ORADB> create table temp (dat   date);
 
Table created.
 
Elapsed: 00:00:02.53
[143]dave44@ORADB> 
[143]dave44@ORADB> insert into temp values (sysdate);
 
1 row created.
 
Elapsed: 00:00:00.70
[143]dave44@ORADB> 
[143]dave44@ORADB> insert into temp values (sysdate-10);
 
1 row created.
 
Elapsed: 00:00:00.53
[143]dave44@ORADB> 
[143]dave44@ORADB> commit;
 
Commit complete.
 
Elapsed: 00:00:00.57
[143]dave44@ORADB> 
[143]dave44@ORADB> select max(dat) from temp;
 
MAX(DAT)
---------
08-MAR-07
 
Elapsed: 00:00:00.92
[143]dave44@ORADB> 
[143]dave44@ORADB> select max(to_char(dat,'dd/mm/yyyy'))
  2  from temp;
 
MAX(TO_CHA
----------
26/02/2007
 
Elapsed: 00:00:00.65
[143]dave44@ORADB> select max(to_char(dat,'mm/dd/yyyy'))
  2  from temp;
 
MAX(TO_CHA
----------
03/08/2007
 

Yeah, that was evident & that was why I used the to_date function.

[Dave44]

so do you understand why there is differences in the results?

[Medhatithi]

Hi Dave,
Yesterday night found a document & that ended all confusions. There it was clearly stated that in a decode statement, if the first return type is null, then the second return type is converted to datatype varchar2. That's why the error was occuring. In CASE-WHEN, this does not happen. Regarding the CASE-WHEN, I did not find any documents, but I am sure it does no such conversion. I executed the query
select max(decode(void ed_flag,'Y',nul l,decode(mtg_st atus_hist.mtg_s tatus,'WD',stat us_date)))
from bravura.mtg_sta tus_hist
where mtg_sk_seq=7063 79
minus
select sysdate ferom dual
.... There was an Oracle error.

But, when I used the CASE-WHEN instead of decode, no errors were thrown. Now, everything seems to fit well & fine. And, nevertheless, THANKS A LOT for your valuable comments on the problem.


You can check the link for the document:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540.pdf

[sreeforu]

Hi Friend,
Please send the table structure and fields presented in that table and send the table name and send what is the problem you are facing?

[Thank You,
Srikanth Reddy.P

Hi,
I am facing a strange problem with decode function in oracle. My table name is status_hist. Below is the query I am hitting on this table:

select max(decode(void ed_flag,'Y',nul l,decode(status _hist.status,'W D',status_date) ))
from status_hist
where sk_seq=6574

The result returned is '29-SEP-05'

However, I checked out the table status_hist to see that for sk_seq=6574 and voided_flag not equals to 'Y' and status='WD', the maximum status_date is '12/22/2005 10:28:29 AM' . However, if I use the CASE-WHEN function, then I get accurate results.

Also, when I hit the query

select max(to_date(dec ode(voided_flag ,'Y',null,decod e(status_hist.s tatus,'WD',stat us_date)),'dd-mon-yy'))
from status_hist
where sk_seq=6574

the result is '12/22/2005'

Can anyone please give an explanation to this?

[Medhatithi]

Hi Friend,
Please send the table structure and fields presented in that table and send the table name and send what is the problem you are facing?

[Thank You,
Srikanth Reddy.P

Hi, I have solved the problem and also mentioned it in the post. You can follow the chain for that purpose. Actually, the problem occured, as for a decode function, if the first return type is null, then the second result type is implicitly converted to varchar2 type. So, I HAD TO USE THE TO_DATE FUNCTION IN PLACE