问题描述
我在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?
推荐答案
嗨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中解码功能的问题...让我发疯的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!