大家晚上好:
现有一sql脚本出错,今天必须要解决的。水平有限,我看了很久也看不出什么错,在此紧急向各位高手求助,还望各位高手能尽快帮我解决,脚本如下:
create table duizhang_yyjw_temp as
select distinct region,com,vc_subno,vc_dissrv,vc_startdate,vc_enddate,vc_addinf,int_yxplanid,vc_rectype,vc_dealdate,vc_logid,vc_applaydate
from duizhang_temp where (region,com,vc_subno,int_yxplanid,vc_dissrv,nvl(vc_addinf,'-'),vc_startdate,
case when vc_enddate is null or vc_enddate >'20200101000000' then '20300101000000'
else to_char(to_date(vc_enddate,'yyyymmddhh24miss')-1/24/60/60,'yyyymmddhh24miss') end) -- 执行时提示这行出错,报 not a valid month 错误
in (
select region,'HW',vc_subno,int_yxplanid,vc_dissrv,DUIZHANG.AF_TRIM(nvl(vc_addinf,'-')),vc_startdate,
case when vc_enddate is null or vc_enddate >'20200101000000' then '20300101000000'
else to_char(to_date(vc_enddate,'yyyymmddhh24miss')-1/24/60/60,'yyyymmddhh24miss') end
from duizhang_temp where com = 'HW'
minus
select region,'HW',vc_subno,int_yxplanid,vc_dissrv,vc_addinf,vc_startdate,case
when vc_enddate > '20200101000000' then '20300101000000' else vc_enddate end
from duizhang_temp where com = 'CX' and vc_source = '1'
)
order by region,vc_applaydate;
执行上面的脚本后,提示上面红色代码行出错,报 ORA-01843: not a valid month 错误,
以上的 vc_enddate 是日期时间型字段,经测试,执行如下的语句都正常:select to_char(to_date(null,'yyyymmddhh24miss')-1/24/60/60,'yyyymmddhh24miss') from dual;select to_char(to_date('','yyyymmddhh24miss')-1/24/60/60,'yyyymmddhh24miss') from dual;由于我的sql水平差,水平有限,实在找不出问题,不知如何解决?
但今天内又必须要完成的任务,所以只好在此向各位高手求助了,还望各位高手帮忙看下,给出解决的方法。
急切待复,衷心感谢!
现有一sql脚本出错,今天必须要解决的。水平有限,我看了很久也看不出什么错,在此紧急向各位高手求助,还望各位高手能尽快帮我解决,脚本如下:
create table duizhang_yyjw_temp as
select distinct region,com,vc_subno,vc_dissrv,vc_startdate,vc_enddate,vc_addinf,int_yxplanid,vc_rectype,vc_dealdate,vc_logid,vc_applaydate
from duizhang_temp where (region,com,vc_subno,int_yxplanid,vc_dissrv,nvl(vc_addinf,'-'),vc_startdate,
case when vc_enddate is null or vc_enddate >'20200101000000' then '20300101000000'
else to_char(to_date(vc_enddate,'yyyymmddhh24miss')-1/24/60/60,'yyyymmddhh24miss') end) -- 执行时提示这行出错,报 not a valid month 错误
in (
select region,'HW',vc_subno,int_yxplanid,vc_dissrv,DUIZHANG.AF_TRIM(nvl(vc_addinf,'-')),vc_startdate,
case when vc_enddate is null or vc_enddate >'20200101000000' then '20300101000000'
else to_char(to_date(vc_enddate,'yyyymmddhh24miss')-1/24/60/60,'yyyymmddhh24miss') end
from duizhang_temp where com = 'HW'
minus
select region,'HW',vc_subno,int_yxplanid,vc_dissrv,vc_addinf,vc_startdate,case
when vc_enddate > '20200101000000' then '20300101000000' else vc_enddate end
from duizhang_temp where com = 'CX' and vc_source = '1'
)
order by region,vc_applaydate;
执行上面的脚本后,提示上面红色代码行出错,报 ORA-01843: not a valid month 错误,
以上的 vc_enddate 是日期时间型字段,经测试,执行如下的语句都正常:select to_char(to_date(null,'yyyymmddhh24miss')-1/24/60/60,'yyyymmddhh24miss') from dual;select to_char(to_date('','yyyymmddhh24miss')-1/24/60/60,'yyyymmddhh24miss') from dual;由于我的sql水平差,水平有限,实在找不出问题,不知如何解决?
但今天内又必须要完成的任务,所以只好在此向各位高手求助了,还望各位高手帮忙看下,给出解决的方法。
急切待复,衷心感谢!
解决方案 »
- ORA-12505: TNS: 监听程序当前无法识别连接描述符中所给出的 SID
- 想把子查询中返回的多个结果拼接成一个字符串,请问怎么搞?在线等,下面是一个例子
- 有谁能够以浅显通俗的语言介绍一下,层次型数据库与关系型数据库的不同之处。
- SQL语言的一个简单问题
- 急!不小心卸载oracle,但是控制文件,数据文件都在,怎样才能恢复呀?
- 在Oracle中如何根据记录行来转变为记录列
- INDEX疑问
- 我现在想学ORACLE,请问到哪里下载入门教程?或者给个入门教程的网址,到处搜了,找不到啊,谢谢!
- 救命啊!!oracle高手来接分!
- 存放用户(system,sys,myuser等)用户密码的表,是哪个表?
- 如果修改序列当前值
- oracle查询中一个超级郁闷的问题,急啊!!!
先更改一下日期格式
select to_char(to_date(sysdate,'yyyymmddhh24miss')-1/24/60/60,'yyyymmddhh24miss') from dual;
就会发现会提示同样的错误,所以去掉to_date应该就可以了,即
to_char(vc_enddate-1/24/60/60,'yyyymmddhh24miss')
你试试
to_char(to_date(vc_enddate, 'yyyymmddhh24miss') - 1 / 24 / 60 / 60,
'yyyymmddhh24miss')
修改为
to_char(vc_enddate, 'yyyymmddhh24miss') - 1 / 24 / 60 / 60
现给予结贴,谢谢各位的帮助!