Oracle中解码功能的问题...让我发疯 [英] Problems with decode function in Oracle ... Turning me mad
问题描述
我在oracle中遇到了解码函数的奇怪问题。我的表名是status_hist。下面是我在这张桌子上打的问题：
选择max（解码（voided_flag，''Y''，null，decode（status_hist .status，''WD''， status_date）））
来自status_hist
其中sk_seq = 6574
返回的结果为''29 -SEP-05' '
但是，我检查了表status_hist，看看对于sk_seq = 6574和voided_flag不等于''Y''和status =''WD''，最大status_date是''12 / 22/2005 10:28:29 AM''。但是，如果我使用CASE-WHEN函数，那么我会得到准确的结果。
另外，当我点击查询时
选择max（to_date（decode（voided_flag，''Y''，null，decode（ sta tus_hist.status，''WD''，status_date）），''dd-mon-yy''））
来自status_hist的
其中sk_seq = 6574
结果是''12 / 22/2005''
任何人都可以对此作出解释吗？
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?
推荐答案
您是否完全根据您的尝试复制了这些陈述？
我问的原因是，在一个不工作的地方，在status_hist和.status之间有一个空格
status_date是一个日期字段还是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 ?
您是否根据您的尝试复制了这些语句？
我问的原因是，在一个不工作的地方，在status_hist和.status之间有一个空格
status_date是一个日期字段还是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 ?
嗨dave，
实际上桌子的名字跟我的不一样我的生产数据库。但是，其余的代码是我在数据库中命中的完全复制品。
status_date的类型为DATE。我已经使用to_date函数alsocheck，在这种情况下，返回的值是正确的，但小时，分钟，第二部分缺失。然后输出''22 -DEC-05''。但是，CASE-WHEN给出了确切的结果。我不知道为什么会发生这种情况
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
嗨dave，
其实这个表的名字与我在生产数据库中的不一样。但是，其余的代码是我在数据库中命中的完全复制品。
status_date的类型为DATE。我已经使用to_date函数alsocheck，在这种情况下，返回的值是正确的，但小时，分钟，第二部分缺失。然后输出''22 -DEC-05''。但是，CASE-WHEN给出了确切的结果。我不知道为什么会发生这种情况
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.
这篇关于Oracle中解码功能的问题...让我发疯的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
