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]

did you copy these statements exactly from what you tried?
reason i ask is that in the one that is not working there is a space between status_hist and the .status

is status_date a date field or varchar2 ?

[Medhatithi]

did you copy these statements exactly from what you tried?
reason i ask is that in the one that is not working there is a space between status_hist and the .status

is status_date a date field or varchar2 ?

Hi dave,
Actually the name of the table is a not the same as I have in my production database. But, the rest of the code is the exact replicate of the one I have hit in my database.
The status_date is of type DATE. I have alsochecked out using to_date function, in that case also, the value returned is right one, but the hour, minute,second part is missing. The output is then '22-DEC-05'. But, the CASE-WHEN gives exact results. I don't know why this is happening

[Dave44]

Hi dave,
Actually the name of the table is a not the same as I have in my production database. But, the rest of the code is the exact replicate of the one I have hit in my database.
The status_date is of type DATE. I have alsochecked out using to_date function, in that case also, the value returned is right one, but the hour, minute,second part is missing. The output is then '22-DEC-05'. But, the CASE-WHEN gives exact results. I don't know why this is happening

Your going to have to help me re create your scenario... cause it seems to work for me.

[141]dave@ORADB> create table status_hist (
  2  sk_seq       number,
  3  status_date  date,
  4  status       varchar2(10),
  5  voided_flag  varchar2(1)
  6  );

Table created.

Elapsed: 00:00:00.03
[141]dave@ORADB> 
[141]dave@ORADB> insert into status_hist 
  2    values (6574, to_date('12/22/2005 10:28:29 AM','mm/dd/yyyy hh:mi:ss pm'),'WD','N');

1 row created.

Elapsed: 00:00:00.00
[141]dave@ORADB> 
[141]dave@ORADB> 
[141]dave@ORADB> select max(decode(voided_flag,'Y',null,decode(status_hist.status,'WD',status_date))
)
  2  from status_hist 
  3  where sk_seq=6574;

MAX(DECOD
---------
22-DEC-05

Elapsed: 00:00:00.01
[141]dave@ORADB> 
[141]dave@ORADB> select max(to_date(decode(voided_flag,'Y',null,decode(status_hist.status,'WD',statu
s_date) ),'dd-mon-yy') )
  2  from status_hist 
  3  where sk_seq=6574;

MAX(TO_DA
---------
22-DEC-05

you shouldnt have to do the to_date in there though... its already a date.

[141]dave@ORADB> SELECT MAX(DECODE(voided_flag, 'Y', NULL, DECODE(status_hist.status, 'WD', status_d
ate) ) ) my_date
  2  FROM   status_hist
  3  WHERE  sk_seq = 6574;

MY_DATE
---------
22-DEC-05

do i have the right data in the table? does the other date that you are getting exist in the table?

[Medhatithi]

Your going to have to help me re create your scenario... cause it seems to work for me.

[141]dave@ORADB> create table status_hist (
  2  sk_seq       number,
  3  status_date  date,
  4  status       varchar2(10),
  5  voided_flag  varchar2(1)
  6  );

Table created.

Elapsed: 00:00:00.03
[141]dave@ORADB> 
[141]dave@ORADB> insert into status_hist 
  2    values (6574, to_date('12/22/2005 10:28:29 AM','mm/dd/yyyy hh:mi:ss pm'),'WD','N');

1 row created.

Elapsed: 00:00:00.00
[141]dave@ORADB> 
[141]dave@ORADB> 
[141]dave@ORADB> select max(decode(voided_flag,'Y',null,decode(status_hist.status,'WD',status_date))
)
  2  from status_hist 
  3  where sk_seq=6574;

MAX(DECOD
---------
22-DEC-05

Elapsed: 00:00:00.01
[141]dave@ORADB> 
[141]dave@ORADB> select max(to_date(decode(voided_flag,'Y',null,decode(status_hist.status,'WD',statu
s_date) ),'dd-mon-yy') )
  2  from status_hist 
  3  where sk_seq=6574;

MAX(TO_DA
---------
22-DEC-05

you shouldnt have to do the to_date in there though... its already a date.

[141]dave@ORADB> SELECT MAX(DECODE(voided_flag, 'Y', NULL, DECODE(status_hist.status, 'WD', status_d
ate) ) ) my_date
  2  FROM   status_hist
  3  WHERE  sk_seq = 6574;

MY_DATE
---------
22-DEC-05

do i have the right data in the table? does the other date that you are getting exist in the table?

Hi Dave,
First of all thanks a lot for the effort you are pouring in. But, the problem is that the table is a huge table and it is not possible to replicate that table in our schema. The problem is that the error I am getting is for this particular table only. For some other tables, the query is running fine. But what actuall I want to know is that why the decode & case-when are giving different results, whereas ideally they should be the same. Also, I know that the status_date is of type date, but when I give the to_date function, then at least the value returned is correct(though minute, hour & second details are not there). Why does this happen? I need to know the logic behind this apparaently impossible phenomenon

[Dave44]

ok, it looks like the decode is returning the data type of the first data type possibility, in this case as a string, therefore, without a to_char immediately around the date field being returned in the second decode function, the system is doing the default date to string output which is just the year month and day info (no hours minutes and seconds and hence why you are losing them).

So try putting the to_Char immediately around the the status_date field.

[153]dave@ORADB> SELECT MAX(DECODE(voided_flag, 'Y', NULL, DECODE(status_hist.status, 'WD', to_Char(
status_date,'mm/dd/yyyy hh:mi:ss pm') ) ) ) my_date
  2  FROM   status_hist
  3  WHERE  sk_seq = 6574;

MY_DATE
----------------------
12/22/2005 10:28:29 am

sorry for the delay in getting back to you... been working some rediculous hours of late.

[Medhatithi]

ok, it looks like the decode is returning the data type of the first data type possibility, in this case as a string, therefore, without a to_char immediately around the date field being returned in the second decode function, the system is doing the default date to string output which is just the year month and day info (no hours minutes and seconds and hence why you are losing them).

So try putting the to_Char immediately around the the status_date field.

[153]dave@ORADB> SELECT MAX(DECODE(voided_flag, 'Y', NULL, DECODE(status_hist.status, 'WD', to_Char(
status_date,'mm/dd/yyyy hh:mi:ss pm') ) ) ) my_date
  2  FROM   status_hist
  3  WHERE  sk_seq = 6574;

MY_DATE
----------------------
12/22/2005 10:28:29 am

sorry for the delay in getting back to you... been working some rediculous hours of late.

Hi Dave,
First of all, the query you posted should be modified a bit using the to_date function. But, my original query was a bit different. What made me go mad was that the disparity between the "CASE-WHEN" and "DECODE" functions. The outcome of the query clearly reveals that since the voided_flag is of type varchar2, the date value returned by decode becomes of type character .. that's why I used the to_date function. But the strange thing is that "CASE-WHEN" is behaving properly. Is this a permanent distinction between these two functions or this is just an isolate incident. What I want to do is to generalize a rule for the decode. Can you provide me any such documents? However, thanks a lot for your concern. I would like to keep this discussion alive. Would appreciate a lot if you can help me out on my query (difference between processing logic of CASE-WHEN & DECODE)

[Medhatithi]

Hi Dave,
First of all, the query you posted should be modified a bit using the to_date function. But, my original query was a bit different. What made me go mad was that the disparity between the "CASE-WHEN" and "DECODE" functions. The outcome of the query clearly reveals that since the voided_flag is of type varchar2, the date value returned by decode becomes of type character .. that's why I used the to_date function. But the strange thing is that "CASE-WHEN" is behaving properly. Is this a permanent distinction between these two functions or this is just an isolate incident. What I want to do is to generalize a rule for the decode. Can you provide me any such documents? However, thanks a lot for your concern. I would like to keep this discussion alive. Would appreciate a lot if you can help me out on my query (difference between processing logic of CASE-WHEN & DECODE)

Hi Dave,
Just checked out something interesting about decode. It can be of help....

This is my test table
ID NAME MARKS ADDRESS FLAG
------ ------------------------- ---------- ------------------------- -
124334 Ayan 75 Chennai Y
143223 Dibendu 84 Hazra N
126466 Avinash 69 Salt Lake N
132443 Raju 66 Rashbihari Y
133246 Bibek 79 Bangalore
133411 Anish 84 Maniktala
133367 Raju 83 Baguihati Y
133332 Sayantan 82 Uttarpara N
133403 Anirban 89 Sovabazar
133388 Rahul Kumar 92 Salt Lake
121212 Sarat 76 Sovabajar Y


I hit this query in the database:
select id, name,
decode(compsc_f lag,'Y',marks,a ddress)
from college;
As expected, an error is returned due to datatype mismatch
But, when I hit the query
select id, name,
decode(compsc_f lag,'Y',address ,marks)
from college;
no error is returned....... ..
CASE WHEN always returns an error in this regard...
select id,(case when compsc_flag='Y' then marks else address end)
from college;


select id,(case when compsc_flag='Y' then address else marks end)
from college;

both return error... We have to convert the marks to to_char to get the correct result.

This seems to be a difference between the two functions and a strange one too

[Dave44]

Hi Dave,
First of all, the query you posted should be modified a bit using the to_date function. But, my original query was a bit different. What made me go mad was that the disparity between the "CASE-WHEN" and "DECODE" functions. The outcome of the query clearly reveals that since the voided_flag is of type varchar2, the date value returned by decode becomes of type character .. that's why I used the to_date function. But the strange thing is that "CASE-WHEN" is behaving properly. Is this a permanent distinction between these two functions or this is just an isolate incident. What I want to do is to generalize a rule for the decode. Can you provide me any such documents? However, thanks a lot for your concern. I would like to keep this discussion alive. Would appreciate a lot if you can help me out on my query (difference between processing logic of CASE-WHEN & DECODE)

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.

[Dave44]

Hi Dave,
Just checked out something interesting about decode. It can be of help....

This is my test table
ID NAME MARKS ADDRESS FLAG
------ ------------------------- ---------- ------------------------- -
124334 Ayan 75 Chennai Y
143223 Dibendu 84 Hazra N
126466 Avinash 69 Salt Lake N
132443 Raju 66 Rashbihari Y
133246 Bibek 79 Bangalore
133411 Anish 84 Maniktala
133367 Raju 83 Baguihati Y
133332 Sayantan 82 Uttarpara N
133403 Anirban 89 Sovabazar
133388 Rahul Kumar 92 Salt Lake
121212 Sarat 76 Sovabajar Y


I hit this query in the database:
select id, name,
decode(compsc_f lag,'Y',marks,a ddress)
from college;
As expected, an error is returned due to datatype mismatch
But, when I hit the query
select id, name,
decode(compsc_f lag,'Y',address ,marks)
from college;
no error is returned....... ..
CASE WHEN always returns an error in this regard...
select id,(case when compsc_flag='Y' then marks else address end)
from college;


select id,(case when compsc_flag='Y' then address else marks end)
from college;

both return error... We have to convert the marks to to_char to get the correct result.

This seems to be a difference between the two functions and a strange one too

right, this makes sense. the decode errors occur because oracle can implicitly convert a number to a string, but you cant convert an alpha-numberic to a number (implicitly or explicitly).

and the case-when requires that all possible outputs are the same datatype. it is interesting though that it doesnt do implicit conversions of dates to chars

[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