如下
Select DEPTID,
sum(decode(RQ,'2009-12-07', (XSJE+XSJE_PF),0)) MON,
sum(decode(RQ,'2009-12-08', (XSJE+XSJE_PF),0)) TUE,
sum(decode(RQ,'2009-12-09', (XSJE+XSJE_PF),0)) WED,
sum(decode(RQ,'2009-12-10', (XSJE+XSJE_PF),0)) THUR,
sum(decode(RQ,'2009-12-11', (XSJE+XSJE_PF),0)) FRI,
sum(decode(RQ,'2009-12-12', (XSJE+XSJE_PF),0)) SAT,
sum(decode(RQ,'2009-12-13', (XSJE+XSJE_PF),0)) SUN
From GZJXCR
Where RQ >= '2009-12-07' and RQ <= '2009-12-13'
Group by DEPTID
是可以的。但在存储过程中
create or replace procedure BB_FDRXSFXBB
(v_HZRQ in Date,
v_FDBH in NUMBER)
is
v_MON_DATE DATE; /*周一*/
v_TUE_DATE DATE; /*周二*/
v_WED_DATE DATE; /*周三*/
v_THUR_DATE DATE; /*周四*/
v_FRI_DATE DATE; /*周五*/
v_SAT_DATE DATE; /*周六*/
v_SUN_DATE DATE; /*周日*/
begin
select trunc(v_HZRQ,'iw') into v_MON_DATE from dual;
select trunc(v_HZRQ,'iw')+1 into v_TUE_DATE from dual;
select trunc(v_HZRQ,'iw')+2 into v_WED_DATE from dual;
select trunc(v_HZRQ,'iw')+3 into v_THUR_DATE from dual;
select trunc(v_HZRQ,'iw')+4 into v_FRI_DATE from dual;
select trunc(v_HZRQ,'iw')+5 into v_SAT_DATE from dual;
select trunc(v_HZRQ,'iw')+6 into v_SUN_DATE from dual;
Select DEPTID,
sum(decode(RQ,'2009-12-07', (XSJE+XSJE_PF),0)) MON,
sum(decode(RQ,'2009-12-08', (XSJE+XSJE_PF),0)) TUE,
sum(decode(RQ,'2009-12-09', (XSJE+XSJE_PF),0)) WED,
sum(decode(RQ,'2009-12-10', (XSJE+XSJE_PF),0)) THUR,
sum(decode(RQ,'2009-12-11', (XSJE+XSJE_PF),0)) FRI,
sum(decode(RQ,'2009-12-12', (XSJE+XSJE_PF),0)) SAT,
sum(decode(RQ,'2009-12-13', (XSJE+XSJE_PF),0)) SUN
From GZJXCR
Where RQ >= v_MON_DATE and RQ <= v_HZRQ
Group by DEPTID;
end;
报错
PLS-00428: an INTO clause is expected in this SELECT statement
请问怎么处理
Select DEPTID,
sum(decode(RQ,'2009-12-07', (XSJE+XSJE_PF),0)) MON,
sum(decode(RQ,'2009-12-08', (XSJE+XSJE_PF),0)) TUE,
sum(decode(RQ,'2009-12-09', (XSJE+XSJE_PF),0)) WED,
sum(decode(RQ,'2009-12-10', (XSJE+XSJE_PF),0)) THUR,
sum(decode(RQ,'2009-12-11', (XSJE+XSJE_PF),0)) FRI,
sum(decode(RQ,'2009-12-12', (XSJE+XSJE_PF),0)) SAT,
sum(decode(RQ,'2009-12-13', (XSJE+XSJE_PF),0)) SUN
From GZJXCR
Where RQ >= '2009-12-07' and RQ <= '2009-12-13'
Group by DEPTID
是可以的。但在存储过程中
create or replace procedure BB_FDRXSFXBB
(v_HZRQ in Date,
v_FDBH in NUMBER)
is
v_MON_DATE DATE; /*周一*/
v_TUE_DATE DATE; /*周二*/
v_WED_DATE DATE; /*周三*/
v_THUR_DATE DATE; /*周四*/
v_FRI_DATE DATE; /*周五*/
v_SAT_DATE DATE; /*周六*/
v_SUN_DATE DATE; /*周日*/
begin
select trunc(v_HZRQ,'iw') into v_MON_DATE from dual;
select trunc(v_HZRQ,'iw')+1 into v_TUE_DATE from dual;
select trunc(v_HZRQ,'iw')+2 into v_WED_DATE from dual;
select trunc(v_HZRQ,'iw')+3 into v_THUR_DATE from dual;
select trunc(v_HZRQ,'iw')+4 into v_FRI_DATE from dual;
select trunc(v_HZRQ,'iw')+5 into v_SAT_DATE from dual;
select trunc(v_HZRQ,'iw')+6 into v_SUN_DATE from dual;
Select DEPTID,
sum(decode(RQ,'2009-12-07', (XSJE+XSJE_PF),0)) MON,
sum(decode(RQ,'2009-12-08', (XSJE+XSJE_PF),0)) TUE,
sum(decode(RQ,'2009-12-09', (XSJE+XSJE_PF),0)) WED,
sum(decode(RQ,'2009-12-10', (XSJE+XSJE_PF),0)) THUR,
sum(decode(RQ,'2009-12-11', (XSJE+XSJE_PF),0)) FRI,
sum(decode(RQ,'2009-12-12', (XSJE+XSJE_PF),0)) SAT,
sum(decode(RQ,'2009-12-13', (XSJE+XSJE_PF),0)) SUN
From GZJXCR
Where RQ >= v_MON_DATE and RQ <= v_HZRQ
Group by DEPTID;
end;
报错
PLS-00428: an INTO clause is expected in this SELECT statement
请问怎么处理
Select DEPTID,
sum(decode(RQ,'2009-12-07', (XSJE+XSJE_PF),0)) MON,
sum(decode(RQ,'2009-12-08', (XSJE+XSJE_PF),0)) TUE,
sum(decode(RQ,'2009-12-09', (XSJE+XSJE_PF),0)) WED,
sum(decode(RQ,'2009-12-10', (XSJE+XSJE_PF),0)) THUR,
sum(decode(RQ,'2009-12-11', (XSJE+XSJE_PF),0)) FRI,
sum(decode(RQ,'2009-12-12', (XSJE+XSJE_PF),0)) SAT,
sum(decode(RQ,'2009-12-13', (XSJE+XSJE_PF),0)) SUN
From GZJXCR
Where RQ >= v_MON_DATE and RQ <= v_HZRQ
Group by DEPTID;
这点,oracle跟sql server不一样
然后使用
open cursor_name for
Select DEPTID,
sum(decode(RQ,'2009-12-07', (XSJE+XSJE_PF),0)) MON,
sum(decode(RQ,'2009-12-08', (XSJE+XSJE_PF),0)) TUE,
sum(decode(RQ,'2009-12-09', (XSJE+XSJE_PF),0)) WED,
sum(decode(RQ,'2009-12-10', (XSJE+XSJE_PF),0)) THUR,
sum(decode(RQ,'2009-12-11', (XSJE+XSJE_PF),0)) FRI,
sum(decode(RQ,'2009-12-12', (XSJE+XSJE_PF),0)) SAT,
sum(decode(RQ,'2009-12-13', (XSJE+XSJE_PF),0)) SUN
From GZJXCR
Where RQ >= v_MON_DATE and RQ <= v_HZRQ
Group by DEPTID;返回结果集。
create or replace procedure BB_FDRXSFXBB
(v_HZRQ in Date,
v_FDBH in NUMBER,
v_out OUT curRecordSet )....
在你的
open v_out for
Select DEPTID,
sum(decode(RQ,'2009-12-07', (XSJE+XSJE_PF),0)) MON,
sum(decode(RQ,'2009-12-08', (XSJE+XSJE_PF),0)) TUE,
sum(decode(RQ,'2009-12-09', (XSJE+XSJE_PF),0)) WED,
sum(decode(RQ,'2009-12-10', (XSJE+XSJE_PF),0)) THUR,
sum(decode(RQ,'2009-12-11', (XSJE+XSJE_PF),0)) FRI,
sum(decode(RQ,'2009-12-12', (XSJE+XSJE_PF),0)) SAT,
sum(decode(RQ,'2009-12-13', (XSJE+XSJE_PF),0)) SUN
From GZJXCR
Where RQ >= v_MON_DATE and RQ <= v_HZRQ
Group by DEPTID;