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

[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