帮我看下下面脚本,运行报错:ORA-01840:输入值对日期格式不够长 (错误的地方已经用红色标出)
唉
怎么改啊?create table t_temp
(
date )
as select
to_date((to_char(l.year)
|| decode (substr(to_char(l.quarter),-1,1), '1','0331', '2','0630', '3' ,'0930', '4','1231')) ,'yyyymmdd')
as date
from company.latest l
where date>=to_date('2007/3/31','yyyy/mm/dd')
order by date;
唉
怎么改啊?create table t_temp
(
date )
as select
to_date((to_char(l.year)
|| decode (substr(to_char(l.quarter),-1,1), '1','0331', '2','0630', '3' ,'0930', '4','1231')) ,'yyyymmdd')
as date
from company.latest l
where date>=to_date('2007/3/31','yyyy/mm/dd')
order by date;
解决方案 »
- PLSQL Developer 连接不上其中的一个服务
- Oracle 11g安装出错,m_bReaderStarted: false,在线等
- Oracle Spatial中金字塔重采样方法问题
- help me; imp and exp Error;
- 谁有oci的long,date,clob,blob等类型字段的操作啊
- BI和Oracle EBS比较如何?
- oracle表扫描问题
- pl/sql developer中能不能不显示查询结果而将结果导出到excel或文本文件中
- 帮帮忙,关于long类型字段的全文检索问题,100分相送
- 如何用OO4O连接远程数据库?
- 关于oracle的小问题求指教
- 对某一列进行求和汇总应该怎样写?
应该有些不符合规则!
|| decode (substr(to_char(quarter),-1,1), '1','0331', '2','0630', '3' ,'0930', '4','1231')) ,'yyyymmdd')
as enddate from company.latest t查询没有任何问题,结果也是对的,但就是建表的时候不对。year是长度为4的字符串,quarter最后一位字符代表季度。这个就是根据year和quarter的写的。
select to_char(mon,'q') q,mon from
(select add_months(to_date('2012'||'-01-31','yyyy-mm-dd'),level-1) mon
from dual
connect by level <= 12) q mon
-------------------------------
1 1 2012/1/31
2 1 2012/2/29
3 1 2012/3/31
4 2 2012/4/30
5 2 2012/5/31
6 2 2012/6/30
7 3 2012/7/31
8 3 2012/8/31
9 3 2012/9/30
10 4 2012/10/31
11 4 2012/11/30
12 4 2012/12/31select mon from
(select add_months(to_date('2012'||'-01-31','yyyy-mm-dd'),level-1) mon
from dual
connect by level <= 12)
where to_char(mon,'q')=3 q mon
------------------------------
7 3 2012/7/31
8 3 2012/8/31
9 3 2012/9/30
year qurater
2010 31
2011 62
2010 44
2011 44
2012 53
2009 62
2009 31
...现在要合并这两列,并且改为日期格式
2010/3/31
2011/6/30
2010/12/31
2011/12/31
2012/9/30
2009/6/30
2009/3/31
...
insert into t1 values (2012,1);
insert into t1 values (2012,2);
insert into t1 values (2012,3);
insert into t1 values (2012,4);select to_date((to_char(a)||decode(substr(b,-1,1), '1','0331','2','0630', '3' ,'0930', '4','1231')) ,'yyyymmdd') a
from t1 a
-------------------------
1 2012/3/31
2 2012/6/30
3 2012/9/30
4 2012/12/31
insert into t1 values (2010,31);
insert into t1 values (2011,62);
insert into t1 values (2010,44);
insert into t1 values (2011,44);
insert into t1 values (2012,53);
insert into t1 values (2009,62);
insert into t1 values (2009,31);select to_date((to_char(year)||decode(substr(qurater,-1,1), '1','0331','2','0630', '3' ,'0930', '4','1231')) ,'yyyymmdd') mon
from t1
mon
------------------------
1 2010/3/31
2 2011/6/30
3 2010/12/31
4 2011/12/31
5 2012/9/30
6 2009/6/30
7 2009/3/31
你语法都没写好,所以不对!
(
dat )
as select
to_date(to_char(l.year)
|| decode (substr(to_char(l.qua),-1,1), '1','0331', '2','0630', '3' ,'0930', '4','1231') ,'yyyymmdd') as datee
from lat l
order by datee一、表的列名,列的别名不能使用date
二、括号有问题
三、where子句中不能使用列别名,order by中可以
create table t_temp
(
enddate )
as select
to_date(to_char(l.t_year)|| decode (substr(to_char(l.t_quarter),-1,1), '1','0331', '2','0630', '3' ,'0930', '4','1231') ,'yyyymmdd')
as enddate
from latest l
where t_year>=2007;
order by enddate; t_year t_quarter
1 2010 1
2 2010 2
3 2010 1
4 2008 3
5 2009 4
6 2009 4
7 2009 4
8 2008 3
9 2008 2
10 2008 4
11 2009 1
...