在开发过程中,通过grouping set函数求聚合值,明细表有timestamp字段,需要先对此字段条件约束后再聚合,结果报错“无效SQL语句”,如果将此条件拿掉,则正常运行;如果将grouping sets改为其它函数:rollup,则正常运行。怀疑是BUG,不知如何解?--测试表结构:create table TMP_HYIN_A
(
DEPTID INTEGER, --机构ID
EMPNID INTEGER, --雇员ID
EMPNAME VARCHAR2(10) --雇员名称,
TTIME TIMESTAMP(6) --时间戳
)
--测试数据DEPTID EMPNID EMPNAME TTIME
1 1 1A 20110307 13:28:50:281000
1 2 1B 20110307 13:28:50:281000
1 3 1C 20110307 13:28:50:281000
2 1 2A 20110307 13:28:50:281000
2 2 2B 20110307 13:28:50:281000需要先对目标表TMP_HYIN_AA字段TTIME进行约束后,再按机构汇总:
数据结果:DEPTID EMPID EMPNAME
1 1 1A
1 2 1B
1 3 1C
1 机构合计 3
2 1 2A
2 2 2B
2 机构合计 2
总计 5
执行SQL:SELECT DECODE(GROUPING_ID(A.DEPTID, A.EMPNID), '3', '总计', A.DEPTID) DEPTID,
DECODE(GROUPING_ID(A.DEPTID, A.EMPNID), '1', '机构合计', A.EMPNID) EMPID,
DECODE(GROUPING_ID(A.EMPNID, A.EMPNAME),
'3',
TO_CHAR(COUNT(*)),
A.EMPNAME) EMPNAME
FROM TMP_HYIN_A A
--WHERE TO_CHAR(A.TTIME,'YYYYMMDD') = '20110101'
GROUP BY GROUPING SETS((DEPTID, EMPNID, EMPNAME),(DEPTID), 1);
如果加上如上WHERE限制,则程序报错“无效SQL语句”;去掉则成功运行;改用SUBSTR(A.TTIME,1,6) = '20110101'则成功;求原因及解决方案!
(
DEPTID INTEGER, --机构ID
EMPNID INTEGER, --雇员ID
EMPNAME VARCHAR2(10) --雇员名称,
TTIME TIMESTAMP(6) --时间戳
)
--测试数据DEPTID EMPNID EMPNAME TTIME
1 1 1A 20110307 13:28:50:281000
1 2 1B 20110307 13:28:50:281000
1 3 1C 20110307 13:28:50:281000
2 1 2A 20110307 13:28:50:281000
2 2 2B 20110307 13:28:50:281000需要先对目标表TMP_HYIN_AA字段TTIME进行约束后,再按机构汇总:
数据结果:DEPTID EMPID EMPNAME
1 1 1A
1 2 1B
1 3 1C
1 机构合计 3
2 1 2A
2 2 2B
2 机构合计 2
总计 5
执行SQL:SELECT DECODE(GROUPING_ID(A.DEPTID, A.EMPNID), '3', '总计', A.DEPTID) DEPTID,
DECODE(GROUPING_ID(A.DEPTID, A.EMPNID), '1', '机构合计', A.EMPNID) EMPID,
DECODE(GROUPING_ID(A.EMPNID, A.EMPNAME),
'3',
TO_CHAR(COUNT(*)),
A.EMPNAME) EMPNAME
FROM TMP_HYIN_A A
--WHERE TO_CHAR(A.TTIME,'YYYYMMDD') = '20110101'
GROUP BY GROUPING SETS((DEPTID, EMPNID, EMPNAME),(DEPTID), 1);
如果加上如上WHERE限制,则程序报错“无效SQL语句”;去掉则成功运行;改用SUBSTR(A.TTIME,1,6) = '20110101'则成功;求原因及解决方案!
--你这个问题应该是to_char() timestamp的问题
--你看看这样行不?
select *
FROM TMP_HYIN_A A
WHERE TO_CHAR(A.TTIME,'YYYYMMDD') = '20110101'
2 ;SYSTIMESTAMP
---------------------------------------------------------------------------
TO_CHAR(SY
----------
07-MAR-11 03.50.14.477327 PM +08:00
2011-03-07如上,没有任何问题,你的A.TTIME是什么类型的?
试试
select TO_CHAR(A.TTIME,'YYYYMMDD') from TMP_HYIN_A
WHERE TO_CHAR(A.TTIME,'YYYYMMDD') = '20110101'
运行结果如何
_______________________________________________
A B
07-3月 -11 03.23.43.640000 下午 +08:00 2011-03-07
select TO_CHAR(A.TTIME,'YYYYMMDD') from TMP_HYIN_A A
WHERE TO_CHAR(A.TTIME,'YYYYMMDD') = '20110307';
_______________________________________________
TO_CHAR(A.TTIME,'YYYYMMDD')
20110307
20110307
20110307
20110307
20110307
可以将:TO_CHAR(A.TTIME,'YYYYMMDD') = '20110101'改为:
( FEETIME BETWEEN TO_TIMESTAMP(WkDate||' 00:00:00:0000','YYYYMMDD HH24:MI:SS:FF') AND TO_TIMESTAMP(WkDate||' 23:59:59:999999','YYYYMMDD HH24:MI:SS:FF'))