iCount NUMBER(10);
iEffCount NUMBER(10);
SELECT
COUNT(*) into iCount, --所有进线数
SUM(CASE WHEN TO_CHAR(t1.calldate,'HH24:MI:SS') >= '11:00' AND TO_CHAR(t1.calldate,'HH24:MI:SS') <= '12:00:00'
THEN '1' ELSE '0' END) into iEffCount --有效进线数
FROM CallHist t1
LEFT JOIN mediaplan t2 ON t1.dnis=t2.dnis
LEFT JOIN PROMOTION t3 ON t2.promotionid=t2.promotionid
WHERE t1.CallType='IN'上面是我存储过程的一部份,编译时,报
SUM(CASE WHEN TO_CHAR(t1.calldate,'HH24:MI:SS') >= '11:00' AND TO_CHAR(t1.calldate,'HH24:MI:SS') <= '12:00:00'
THEN '1' ELSE '0' END) into iEffCount --有效进线数
这段语句这个错误,这是怎么回事呀??
PROCEDURE IAGENT.RPT_MEDIACOUNT 编译错误
错误:PL/SQL: ORA-00934: 此处不允许使用分组函数
行:58
文本:THEN '1' ELSE '0' END) into iEffCount --有效进线数错误:PL/SQL: SQL Statement ignored
行:56
文本:COUNT(*) into iCount, --所有进线数
iEffCount NUMBER(10);
SELECT
COUNT(*) into iCount, --所有进线数
SUM(CASE WHEN TO_CHAR(t1.calldate,'HH24:MI:SS') >= '11:00' AND TO_CHAR(t1.calldate,'HH24:MI:SS') <= '12:00:00'
THEN '1' ELSE '0' END) into iEffCount --有效进线数
FROM CallHist t1
LEFT JOIN mediaplan t2 ON t1.dnis=t2.dnis
LEFT JOIN PROMOTION t3 ON t2.promotionid=t2.promotionid
WHERE t1.CallType='IN'上面是我存储过程的一部份,编译时,报
SUM(CASE WHEN TO_CHAR(t1.calldate,'HH24:MI:SS') >= '11:00' AND TO_CHAR(t1.calldate,'HH24:MI:SS') <= '12:00:00'
THEN '1' ELSE '0' END) into iEffCount --有效进线数
这段语句这个错误,这是怎么回事呀??
PROCEDURE IAGENT.RPT_MEDIACOUNT 编译错误
错误:PL/SQL: ORA-00934: 此处不允许使用分组函数
行:58
文本:THEN '1' ELSE '0' END) into iEffCount --有效进线数错误:PL/SQL: SQL Statement ignored
行:56
文本:COUNT(*) into iCount, --所有进线数
COUNT(*) into iCount, --所有进线数
SUM(CASE WHEN TO_CHAR(t1.calldate,'HH24:MI:SS') >= '11:00' AND TO_CHAR(t1.calldate,'HH24:MI:SS') <= '12:00:00'
THEN '1' ELSE '0' END) into iEffCount --有效进线数
FROM CallHist t1
LEFT JOIN mediaplan t2 ON t1.dnis=t2.dnis
LEFT JOIN PROMOTION t3 ON t2.promotionid=t2.promotionid
WHERE t1.CallType='IN'这个语句很有问题,怎么count(*)和case when在同一句啊?
SELECT
Employee.Employee_No,
COUNT(1) AS TotalCount,
SUM(Order_Total) AS SumTotal,
SUM(CASE WHEN CaseSource_ID_A ='CO-200510230001' THEN Order_Total ELSE 0 END)AS SumInputOrderTotal,
SUM(CASE WHEN CaseSource_ID_A ='CO-200510230001' THEN 1 ELSE 0 END)AS InCount,
SUM(CASE WHEN (CaseSource_ID_A<>'CO-200510230001' OR CaseSource_ID_A IS NULL)
THEN Order_Total ELSE 0 END)AS SumOutputOrderTotal,
SUM(CASE WHEN (CaseSource_ID_A<>'CO-200510230001' OR CaseSource_ID_A IS NULL)
THEN 1 ELSE 0 END)AS OutCount
FROM [Order]
INNER JOIN Employee ON [Order].Employee_ID_A=Employee.Employee_ID_A
GROUP BY Employee.Employee_No
类似
count(*),count(decode(字段,符合条件,1,nul))
或者
count(*),sum(decode(字段,符合条件,1,0))
现在将我的代码贴出:
SELECT
COUNT(*) INTO iCount, --所有进线数
/*
SUM(CASE WHEN TO_CHAR(t1.calldate,'HH24:MI:SS') >= '11:00:00' AND TO_CHAR(t1.calldate,'HH24:MI:SS') <= '12:00:00'
THEN '1' ELSE '0' END) INTO iEffCount --有效进线数
*/
sum(decode(TO_CHAR(t1.calldate,'HH24:MI:SS'),>= '11:00:00' and <= '12:00:00',1,0))INTO iEffCount
FROM CALLHIST t1
LEFT JOIN MEDIAPLAN t2 ON t1.dnis=t2.dnis
LEFT JOIN PROMOTION t3 ON t2.promotionid=t2.promotionid
WHERE 1=1 AND t1.CallType='IN'
AND TO_CHAR(t1.calldate,'YYYY-MM-DD')=TO_CHAR(begindate,'YYYY-MM-DD')
--and TO_CHAR(t1.calldate,'YYYY-MM-DD')<=TO_CHAR(enddate,'YYYY-MM-DD')
AND TO_CHAR(t1.calldate,'MM-DD')>=TO_CHAR(sBeginDate,'MM-DD')
AND TO_CHAR(t1.calldate,'MM-DD')<=TO_CHAR(sEndDate,'MM-DD')
AND t3.prodid=sProductID
AND t2.COMPANY=sMediaID
group by t3.prodid;
行:63
文本:FROM CALLHIST t1错误:PL/SQL: SQL Statement ignored
行:57
文本:COUNT(*) INTO iCount, --所有进线数
好像不能这样写吧!
还是用case when你先运行sql,你写的sql不能写group by t3.prodid;
如果你要写的话,在select 中增加 t3.prodid
COUNT(*) iCount, --所有进线数
SUM(CASE WHEN TO_CHAR(t1.calldate,'HH24:MI:SS') >= '11:00:00' AND TO_CHAR(t1.calldate,'HH24:MI:SS') <= '12:00:00'
THEN '1' ELSE '0' END) iEffCount --有效进线数
FROM CALLHIST t1
LEFT JOIN MEDIAPLAN t2 ON t1.dnis=t2.dnis
LEFT JOIN PROMOTION t3 ON t2.promotionid=t2.promotionid
WHERE 1=1 AND t1.CallType='IN'
AND TO_CHAR(t1.calldate,'YYYY-MM-DD')=TO_CHAR(begindate,'YYYY-MM-DD')
--and TO_CHAR(t1.calldate,'YYYY-MM-DD')<=TO_CHAR(enddate,'YYYY-MM-DD')
AND TO_CHAR(t1.calldate,'MM-DD')>=TO_CHAR(sBeginDate,'MM-DD')
AND TO_CHAR(t1.calldate,'MM-DD')<=TO_CHAR(sEndDate,'MM-DD')
AND t3.prodid=sProductID
AND t2.COMPANY=sMediaID
group by t3.prodid;
这语句拿到查询分析器里单独是可以执行的,拿到存储过程中,加入into语句一起编号时,就报错,我操Oralce CEO 他妈,就是不行,我觉的调试这块,Oracle不如SQL Server,垃圾,fuck!!!
iEffCount NUMBER(10);
SELECT COUNT(*), --ËùÓнøÏßÊý
SUM(CASE WHEN to_char(t1.calldate, 'HH24:MI:SS') >= '11:00:00' AND to_char(t1.calldate, 'HH24:MI:SS') <= '12:00:00' THEN '1'
ELSE '0' END)
INTO icount, ieffcount --ÓÐЧ½øÏßÊý
FROM callhist t1
LEFT JOIN mediaplan t2 ON t1.dnis = t2.dnis
LEFT JOIN promotion t3 ON t2.promotionid = t2.promotionid
WHERE t1.calltype = 'IN'
------
基本语法问题:
SQL SERVER:
select v_a=col1, v_b=col2 from xxxx;
ORACLE:
select col1, col2 into v_a, v_b from xxxx;
SUM(CASE WHEN to_char(t1.calldate, 'HH24:MI:SS') >= '11:00:00' AND to_char(t1.calldate, 'HH24:MI:SS') <= '12:00:00' THEN '1'
ELSE '0' END)
---------------------------------------------------------------------
SUM(CASE WHEN to_char(t1.calldate, 'HH24:MI:SS') >= '11:00:00' AND to_char(t1.calldate, 'HH24:MI:SS') <= '12:00:00' THEN 1
ELSE 0 END)
中的'1'改为1;'0'改为0,因为在SUM中