各位大神,我的一个sql语句报错,语句如下:
SELECT *
FROM (SELECT D.AAC999,
D.AAC003,
D.AAC147,
D.AAC009,
B.AAE140,
B.AAC066,
E.AAA119,
F.AAB999,
F.AAB004,
G.CAE249,
G.AAE217,
A.AAA121,
A.AAE225 CAE179,
A.AAE226 CAE180,
NULL CAE181,
NULL CAE182,
B.AAA027,
B.AAB301,
CASE
WHEN LENGTH(A.AAE225) = 6 AND LENGTH(A.AAE226) = 6 THEN
MONTHS_BETWEEN(TO_DATE(A.AAE226, 'yyyymm'),
TO_DATE(A.AAE225, 'yyyymm'))
WHEN LENGTH(A.AAE225) = 4 AND LENGTH(A.AAE226) = 4 THEN
MONTHS_BETWEEN(TO_DATE(A.AAE226, 'yyyy'),
TO_DATE(A.AAE225, 'yyyy'))
END XX
FROM AC40 A, AC42 B, TC95 C, AC01 D, AC50 E, AB01 F, AE02 G
WHERE A.AAZ192 = B.AAZ192
AND A.AAC001 = C.AAC001
AND A.AAC001 = D.AAC001
AND A.AAC001 = E.AAC001
AND B.AAB001 = F.AAB001
AND A.AAZ002 = G.AAZ002
AND E.AAE100 = '1'
AND E.AAE140 = B.AAE140
AND A.AAE100 = '1'
AND B.AAE100 = '1'
AND B.AAE017 = '0'
AND B.AAA115 IN ('20', '21', '26', '2C')
AND B.AAE002 >= SUBSTR(201606, 1, 6)
AND B.AAE002 <= SUBSTR(201606, 1, 6)
GROUP BY D.AAC999,
D.AAC003,
D.AAC147,
D.AAC009,
B.AAE140,
B.AAC066,
E.AAA119,
F.AAB999,
F.AAB004,
G.CAE249,
G.AAE217,
A.AAA121,
A.AAE225,
A.AAE226,
B.AAA027,
B.AAB301)
WHERE XX > 6;原因是表中to_date的两个日期字段表中的数据有异常数据,但是现在的问题是to_date函数全表扫描了,而不是处理where语句筛过的数据了,这种情况该怎么解决?感谢各位!
SELECT *
FROM (SELECT D.AAC999,
D.AAC003,
D.AAC147,
D.AAC009,
B.AAE140,
B.AAC066,
E.AAA119,
F.AAB999,
F.AAB004,
G.CAE249,
G.AAE217,
A.AAA121,
A.AAE225 CAE179,
A.AAE226 CAE180,
NULL CAE181,
NULL CAE182,
B.AAA027,
B.AAB301,
CASE
WHEN LENGTH(A.AAE225) = 6 AND LENGTH(A.AAE226) = 6 THEN
MONTHS_BETWEEN(TO_DATE(A.AAE226, 'yyyymm'),
TO_DATE(A.AAE225, 'yyyymm'))
WHEN LENGTH(A.AAE225) = 4 AND LENGTH(A.AAE226) = 4 THEN
MONTHS_BETWEEN(TO_DATE(A.AAE226, 'yyyy'),
TO_DATE(A.AAE225, 'yyyy'))
END XX
FROM AC40 A, AC42 B, TC95 C, AC01 D, AC50 E, AB01 F, AE02 G
WHERE A.AAZ192 = B.AAZ192
AND A.AAC001 = C.AAC001
AND A.AAC001 = D.AAC001
AND A.AAC001 = E.AAC001
AND B.AAB001 = F.AAB001
AND A.AAZ002 = G.AAZ002
AND E.AAE100 = '1'
AND E.AAE140 = B.AAE140
AND A.AAE100 = '1'
AND B.AAE100 = '1'
AND B.AAE017 = '0'
AND B.AAA115 IN ('20', '21', '26', '2C')
AND B.AAE002 >= SUBSTR(201606, 1, 6)
AND B.AAE002 <= SUBSTR(201606, 1, 6)
GROUP BY D.AAC999,
D.AAC003,
D.AAC147,
D.AAC009,
B.AAE140,
B.AAC066,
E.AAA119,
F.AAB999,
F.AAB004,
G.CAE249,
G.AAE217,
A.AAA121,
A.AAE225,
A.AAE226,
B.AAA027,
B.AAB301)
WHERE XX > 6;原因是表中to_date的两个日期字段表中的数据有异常数据,但是现在的问题是to_date函数全表扫描了,而不是处理where语句筛过的数据了,这种情况该怎么解决?感谢各位!
解决方案 »
- oracle References
- 这个功能能用SQL语句实现吗?
- 这两个sql为什么不一样
- Apache、ORACLE、PHP都已经正确安装配置完成,初次运行sugarCRM,系统会自动进入安装配置页面,为什么配置PHP的时候只有MYSQL没有ORACLE选项?在线等,谢谢
- 好急 关于oracle10G 数据库链接问题
- 帮个忙吧,发个CNOUG(http://www.oracle.com.cn/)的邀请码,谢谢!!
- select col,* from tablename Oracle不支持这样的写法?
- 急......小弟在线等侍..回复有分
- sql*Plus 为何不能查询自创的表,在线等
- 为什么我的OMS打开没有数据库呢???
- 求大神给个linux下安装rac集群详细教程或有这中书的推荐。
- Oracle同一个存储过程,不同参数,运行多个效率很差求解
sql语句的执行顺序是先select 得到结果集1,然后根据where条件筛选结果集1得到结果集2
就是单独执行select * 里面的没问题,外面套上一层并且加上xx>6条件后报错,报了里面to_date的错误,日期格式不对。
就是单独执行select * 里面的没问题,外面套上一层并且加上xx>6条件后报错,报了里面to_date的错误,日期格式不对。
又是这么一个奇葩的问题加我q吧( 212646490 ),详细唠唠……
AAE225
AAE226
以上两列的字符类型和具体数据(举例即可)