本文介绍了Oracle中解码功能的问题...让我发疯的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



我在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.

展开 | 选择 | Wrap | 行号


这篇关于Oracle中解码功能的问题...让我发疯的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-19 19:21
查看更多