/************Oracle9i下脚本*****************/ SELECT BeginTime, tmpI2 = SUM(CASE WHEN (ISNULL(CallBegin, 0) < ISNULL(CallEnd, 0)) OR (ISNULL(AckBegin, 0) < ISNULL(AckEnd, 0)) THEN 1 ; ELSE 0 ; END case;), tmpI3 = SUM(CASE WHEN (ISNULL(AckBegin, 0) < ISNULL(AckEnd, 0)) AND (ISNULL(CallBegin, 1) = ISNULL(CallEnd, 0)) THEN 1 ; ELSE 0 ; END case;) FROM t1
不能用tmpI2 = SUM(CASE WHEN .....等,应改成SUM(CASE WHEN ..... as tmpI2
试试吧,应该可以的 select begintime,sum(decode(sign(nvl(callbegin,0),nvl(callend,0)),-1,1,0)+sum(sign(nvl(ackbegin,0),nvl(ackend,0)),-1,1,0)),sum(decode(sign(nvl(ackbegin,0),nvl(ackend,0)),-1,decode(sign(nvl(callbegin,1),nvl(callend,0)),0,1,0))) into tmp1,tmp2,tmp3 from t1
callbegin和callend等都是date类型的如果用8i的话,应当是我上面的教本吧 上面那段代码是接在下面的, insert into t3 *****9i的话,应当就是小龙的代码吧 我再测试一下
SELECT
BeginTime,
tmpI2 = SUM(CASE WHEN (ISNULL(CallBegin, 0) < ISNULL(CallEnd, 0)) OR (ISNULL(AckBegin, 0) < ISNULL(AckEnd, 0)) THEN 1 ;
ELSE 0 ;
END case;),
tmpI3 = SUM(CASE WHEN (ISNULL(AckBegin, 0) < ISNULL(AckEnd, 0)) AND (ISNULL(CallBegin, 1) = ISNULL(CallEnd, 0)) THEN 1 ;
ELSE 0 ;
END case;)
FROM t1
select begintime,sum(decode(sign(nvl(callbegin,0),nvl(callend,0)),-1,1,0)+sum(sign(nvl(ackbegin,0),nvl(ackend,0)),-1,1,0)),sum(decode(sign(nvl(ackbegin,0),nvl(ackend,0)),-1,decode(sign(nvl(callbegin,1),nvl(callend,0)),0,1,0))) into tmp1,tmp2,tmp3 from t1
上面那段代码是接在下面的,
insert into t3
*****9i的话,应当就是小龙的代码吧
我再测试一下