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?
16 7289
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_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
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_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
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
event_end 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 (null)
where the customer does not have and address2, address 3,
At present its set up to read customers
ADDRESS1
|
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 Informationstechnologien mbH
Schoeib Amin
Tel. +49 (0) 69 697688-132
| |
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 pertaining to Databases into a description.
For example,
(1)CIS 143 to be decoded to Introduction to SQL.,
(2)CIS 090 to be decoded to Introduction to VISUAL BASIC.,
(3)CIS 145 to be decoded to Introduction to ORACLE.,
(4)CIS 147 to be decoded...
|
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 TB_TOWN_STREET
WHERE upper(trim(STREETNAME)) = upper(trim(STREET))
AND upper(trim(TOWN_NAME)) = upper(trim(TOWN))
|
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 me to
using 'case' but no matter how I write it I can't get it to work and
get a syntax error. Suggestions?
select SYST CTR
, isnull(substring(CD_A, 1, 3), ' ') RESCODE
, DES DESCRIPTION
, decode (substring(CD_A, 1, 3), CODE,PRICE,0)...
|
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 - DECODE(STATS_DAILY_SA.EXCEPTION_TIME, Null, 0,
STATS_DAILY_SA.EXCEPTION_TIME)) THEN 0 ELSE ((STATS_DAILY_SA.SCHEDULED_TIME - DECODE(STATS_DAILY_SA.EXCEPTION_TIME, Null, 0,
STATS_DAILY_SA.EXCEPTION_TIME)) - STATS_DAILY_SA.LOGIN_TIME)...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |