INSERT INTO t_Tmp_TelephonistWorkSum
(
LogDate,
StaffNo,
SEndCount
)
(SELECT
v_STValue,
a.staffno,
(SELECT COUNT(*)
FROM vi_histable b
WHERE staffno = a.staffno
AND timestp < TO_CHAR(v_BeginTRet, 'yyyy/mm/dd hh24:mi:ss')
AND timestp >= TO_CHAR(v_UpTime, 'yyyy/mm/dd hh24:mi:ss')) AS SEndCount
FROM vi_acceptserial a
GROUP BY a.staffno);保证你的select语句能执行正确。
(
LogDate,
StaffNo,
SEndCount
)
(SELECT
v_STValue,
a.staffno,
(SELECT COUNT(*)
FROM vi_histable b
WHERE staffno = a.staffno
AND timestp < TO_CHAR(v_BeginTRet, 'yyyy/mm/dd hh24:mi:ss')
AND timestp >= TO_CHAR(v_UpTime, 'yyyy/mm/dd hh24:mi:ss')) AS SEndCount
FROM vi_acceptserial a
GROUP BY a.staffno);保证你的select语句能执行正确。
INSERT INTO t_Tmp_TelephonistWorkSum
(
LogDate,
StaffNo,
SEndCount
)
SELECT
v_STValue,
a.staffno,
COUNT(*)
FROM vi_acceptserial a,vi_histable b
WHERE a.staffno = a.staffno
AND b.timestp < TO_CHAR(v_BeginTRet, 'yyyy/mm/dd hh24:mi:ss')
AND b.timestp >= TO_CHAR(v_UpTime, 'yyyy/mm/dd hh24:mi:ss')
GROUP BY a.staffno;
(
LogDate,
StaffNo,
SEndCount,
RecordCount,
TimerCount,
DeleteCount,
QueryCount
)
SELECT
v_STValue,
staffno,
(SELECT COUNT(1)
FROM vi_histable
WHERE staffno = a.staffno
AND timestp < TO_CHAR(v_BeginTRet, 'yyyy/mm/dd hh24:mi:ss')
AND timestp >= TO_CHAR(v_UpTime, 'yyyy/mm/dd hh24:mi:ss')) AS SEndCount,
(SELECT COUNT(1)
FROM vi_acceptserial
WHERE staffno=a.staffno
AND refertime >= v_BeginTRet
AND refertime < v_UpTime
AND servetype = '2000') AS RecordCount,
(SELECT COUNT(1)
FROM vi_acceptserial
WHERE staffno=a.staffno
AND refertime >= v_BeginTRet
AND refertime < v_UpTime
AND servetype = '2003') AS TimerCount,
(SELECT COUNT(1)
FROM vi_acceptserial
WHERE staffno=a.staffno
AND refertime >= v_BeginTRet
AND refertime < v_UpTime
AND servetype = '0002'
AND operationtype = 'D') AS DeleteCount,
(SELECT COUNT(1)
FROM vi_acceptserial
WHERE staffno=a.staffno
AND refertime >= v_BeginTRet
AND refertime < v_UpTime
AND servetype = '0002'
AND operationtype = 'Q') AS QueryCount
FROM vi_acceptserial a, vi_histable b
GROUP BY staffno;
9i开始才支持Scalar Subqueries
INSERT INTO t_Tmp_TelephonistWorkSum
(LogDate, StaffNo,SEndCount,RecordCount)
select v_STValue,t.staff_no,t1.SEndCount,t2.RecordCount
from (
SELECT staffno,COUNT(1) SEndCount
FROM vi_histable
WHERE timestp < TO_CHAR(v_BeginTRet, 'yyyy/mm/dd hh24:mi:ss')
AND timestp >= TO_CHAR(v_UpTime, 'yyyy/mm/dd hh24:mi:ss')
) t1,
(SELECT staff_no,COUNT(1) RecordCount
FROM vi_acceptserial
WHERE refertime >= v_BeginTRet
AND refertime < v_UpTime
AND servetype = '2000'
) t2
,vi_acceptserial t
where t.staff_no=t1.staff_no(+)
and t.staff_no=t2.staff_no(+)说明:vi_acceptserial 表中的staff_no是唯一记录吧,不是的话上面的sql就不能这么写,你的sql同样也有问题。
实面不行,把(select distinct staff_no from vi_acceptserial ) 代替上面的vi_acceptserial t吧