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(voided_flag,'Y',null,decode(status_hist .status,'WD',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(decode(voided_flag,'Y',null,decode(sta tus_hist.status,'WD',status_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?
16 7246
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 ?
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
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?
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
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.
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)
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_flag,'Y',marks,address)
from college;
As expected, an error is returned due to datatype mismatch
But, when I hit the query
select id, name,
decode(compsc_flag,'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
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.
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_flag,'Y',marks,address)
from college;
As expected, an error is returned due to datatype mismatch
But, when I hit the query
select id, name,
decode(compsc_flag,'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
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_field) 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
-
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.status='WD' then status_date else null end)end) from status_hist
where sk_seq=706379
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_field) 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.
so do you understand why there is differences in the results?
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(voided_flag,'Y',null,decode(mtg_status_ hist.mtg_status,'WD',status_date)))
from bravura.mtg_status_hist
where mtg_sk_seq=706379
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
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(voided_flag,'Y',null,decode(status_hist .status,'WD',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(decode(voided_flag,'Y',null,decode(sta tus_hist.status,'WD',status_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?
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Walt |
last post by:
Hi,
I want to write a query that returns Y or N depending on whether the
current time is within a specified window i.e.
EVENT_TABLE
----------
event_id number PK
event_start date...
|
by: Mark Hoffman |
last post by:
I'm a newbie at Oracle..Be gentle!
I have a table that stores information (WMI data) about computers on
our network. The table looks like:
ComputerID
ItemID
Class
Property
Value
|
by: etravels |
last post by:
Hiyas
I am currently working on Oracle Report which ties in with my a report
in the ebusiness suite of Oracle telesales module.
Basically, the address on statement reports have blank fields...
|
by: N |
last post by:
What is the function in SQL that works like DECODE in Oracle?"
Thanks,
N
|
by: Amin Schoeib |
last post by:
Hi,
Like I see there is no equivalent to the Oracle decode Function
In Postgres.Is there maybe somebody who wrote decode as a
Function?
Schoeib
4Tek Gesellschaft für angewandte...
|
by: Deven Oza |
last post by:
Hello everyone
Please help me for the following query
I want to solve the query Using the (DECODE) function, I want to write a program segment that will successfully decode all the CIS courses...
|
by: Mikeland |
last post by:
I come from the oracle world and would use the Oracle 'DECODE'
function
extensively. Is there any equivalent in db2???
using db2 version 8.1 on win
SELECT COUNT(*) INTO VAR_COUNT_STREET
FROM...
|
by: dan-x |
last post by:
I am a novice to SQL Server, so this is probably a really easy problem
to fix. I'm translating an Oracle query and need to change the
'decode' to something compatible. Everything I've read points...
|
by: ollyb303 |
last post by:
Hello,
I have been using the following expression in Access as part of a statement to query an Oracle database:
(Sum(CASE WHEN STATS_DAILY_SA.LOGIN_TIME > (STATS_DAILY_SA.SCHEDULED_TIME -...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |