oracle sql语句中to_date函数报错问题
各位大神,我的一个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语句筛过的数据了,这种情况该怎么解决?感谢各位!