where qcr.approveDate=to_date(to_char(trunc(sysdate),'yyyy-mm'),''yyyy-mm'')
where 条件那里传的是月份 就是1月的时候是就是上面的语句2-12月的时候就是往1月建的表当中添加数据
到2-12月的时候就不是创建表了 而是 insert into JCJGMONTHTABLE( select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,qii.jcjgid, qcr.approveDate, count(*) as SAMPLEGROUPID, sum(case when qcr.result in('A') then 1 else 0 end) RESULT_A, --结论 sum(case when qcr.RESULT in('B') then 1 else 0 end) RESULT_B, sum(case when qcr.RESULT in('D') then 1 else 0 end) RESULT_D, sum(case when qcr.RESULT in('C') then 1 else 0 end) RESULT_C, sum(case when qcr.RESULT in('E') then 1 else 0 end) RESULT_E, sum(case when qcr.processstate in('N') then 1 else 0 end) PROCESSSTATE,--曲线比对异常 sum(case when qcr.experimentationstate in('N') then 1 else 0 end) EXPERIMENTATIONSTATE, --比对结果异常 sum(case when qcr.echosampleid is not null then 1 else 0 end) ECHOSAMPLEID_TRUE, --复检的样品编号_是 sum(case when qcr.echosampleid is null then 1 else 0 end) ECHOSAMPLEID_FALSE,--复检的样品编号_否 sum(case when qcr.realtimestate in('N') then 1 else 0 end) REALTIMESTATE,--是否实时传输状态 sum(case when qcr.modifystate in('Y') then 1 else 0 end) MODIFYSTATE, --数据被修改 sum(case when qcr.collectionstate in('N') then 1 else 0 end) COLLECTIONSTATE --未采集曲线数据 from report qcr inner join compact jc on qcr.compactpid = jc.compactpid inner join inspectinstitution qii on qcr.jcjgid = qii.jcjgid where qcr.approveDate=to_date(''2012-2'',''yyyy-mm'') group by qii.inspectinstitutionpid,qii.inspectinstitutionName,qii.jcjgid,qcr.approveDate));
create or replace procedure JDZMONTHCOUNT(approvedate date) authid current_user is vn_ctn number; begin select count(*) into vn_ctn from user_all_tables a where a.table_name like upper('JDZMONTHTABLE'); if vn_ctn > 0 then execute immediate 'drop table JDZMONTHTABLE'; end if; execute immediate 'create table JDZMONTHTABLE as select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,jc.districTid,qcr.approveDate, count(*) as SAMPLEGROUPID, sum(case when qcr.result in(''A'') then 1 else 0 end) "RESULT_A", --结论 sum(case when qcr.RESULT in(''B'') then 1 else 0 end) "RESULT_B", sum(case when qcr.RESULT in(''D'') then 1 else 0 end) "RESULT_D", sum(case when qcr.RESULT in(''C'') then 1 else 0 end) "RESULT_C", sum(case when qcr.RESULT in(''E'') then 1 else 0 end) "RESULT_E", sum(case when qcr.processstate in(''N'') then 1 else 0 end) "PROCESSSTATE",--曲线比对异常 sum(case when qcr.experimentationstate in(''N'') then 1 else 0 end) "EXPERIMENTATIONSTATE", --比对结果异常 sum(case when qcr.echosampleid is not null then 1 else 0 end) "ECHOSAMPLEID_TRUE", --复检的样品编号_是 sum(case when qcr.echosampleid is null then 1 else 0 end) "ECHOSAMPLEID_FALSE",--复检的样品编号_否 sum(case when qcr.realtimestate in(''N'') then 1 else 0 end) "REALTIMESTATE",--是否实时传输状态 sum(case when qcr.modifystate in(''Y'') then 1 else 0 end) "MODIFYSTATE", --数据被修改 sum(case when qcr.collectionstate in(''N'') then 1 else 0 end) "COLLECTIONSTATE" --未采集曲线数据 from report qcr inner join compact jc on qcr.compactpid = jc.compactpid inner join inspectinstitution qii on jc.districtid = qii.jcjgid where qcr.approveDate=to_date(''2012-1'',''yyyy-mm'') group by qii.inspectinstitutionpid,qii.inspectinstitutionName,jc.districTid,qcr.approveDate )'; for p in 2..12 loop if p<=12 then insert into JDZMONTHTABLE(select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,jc.districTid,qcr.approveDate, count(*) as SAMPLEGROUPID, sum(case when qcr.result in('A') then 1 else 0 end) "RESULT_A", --结论 sum(case when qcr.RESULT in('B') then 1 else 0 end) "RESULT_B", sum(case when qcr.RESULT in('D') then 1 else 0 end) "RESULT_D", sum(case when qcr.RESULT in('C') then 1 else 0 end) "RESULT_C", sum(case when qcr.RESULT in('E') then 1 else 0 end) "RESULT_E", sum(case when qcr.processstate in('N') then 1 else 0 end) "PROCESSSTATE",--曲线比对异常 sum(case when qcr.experimentationstate in('N') then 1 else 0 end) "EXPERIMENTATIONSTATE", --比对结果异常 sum(case when qcr.echosampleid is not null then 1 else 0 end) "ECHOSAMPLEID_TRUE", --复检的样品编号_是 sum(case when qcr.echosampleid is null then 1 else 0 end) "ECHOSAMPLEID_FALSE",--复检的样品编号_否 sum(case when qcr.realtimestate in('N') then 1 else 0 end) "REALTIMESTATE",--是否实时传输状态 sum(case when qcr.modifystate in('Y') then 1 else 0 end) "MODIFYSTATE", --数据被修改 sum(case when qcr.collectionstate in('N') then 1 else 0 end) "COLLECTIONSTATE" --未采集曲线数据 from report qcr inner join compact jc on qcr.compactpid = jc.compactpid inner join inspectinstitution qii on jc.districtid = qii.jcjgid where qcr.approveDate=to_date('2012-'||p||'','yyyy-mm') group by qii.inspectinstitutionpid,qii.inspectinstitutionName,jc.districTid,qcr.approveDate ) end if; end loop; exception when others then null; end JDZMONTHCOUNT; 解决了
select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,qii.jcjgid,
qcr.approveDate,
count(*) as SAMPLEGROUPID,
sum(case when qcr.result in('A') then 1 else 0 end) RESULT_A, --结论
sum(case when qcr.RESULT in('B') then 1 else 0 end) RESULT_B,
sum(case when qcr.RESULT in('D') then 1 else 0 end) RESULT_D,
sum(case when qcr.RESULT in('C') then 1 else 0 end) RESULT_C,
sum(case when qcr.RESULT in('E') then 1 else 0 end) RESULT_E,
sum(case when qcr.processstate in('N') then 1 else 0 end) PROCESSSTATE,--曲线比对异常
sum(case when qcr.experimentationstate in('N') then 1 else 0 end) EXPERIMENTATIONSTATE, --比对结果异常
sum(case when qcr.echosampleid is not null then 1 else 0 end) ECHOSAMPLEID_TRUE, --复检的样品编号_是
sum(case when qcr.echosampleid is null then 1 else 0 end) ECHOSAMPLEID_FALSE,--复检的样品编号_否
sum(case when qcr.realtimestate in('N') then 1 else 0 end) REALTIMESTATE,--是否实时传输状态
sum(case when qcr.modifystate in('Y') then 1 else 0 end) MODIFYSTATE, --数据被修改
sum(case when qcr.collectionstate in('N') then 1 else 0 end) COLLECTIONSTATE --未采集曲线数据
from report qcr
inner join compact jc on qcr.compactpid = jc.compactpid
inner join inspectinstitution qii on qcr.jcjgid = qii.jcjgid
where qcr.approveDate=to_date(''2012-2'',''yyyy-mm'')
group by qii.inspectinstitutionpid,qii.inspectinstitutionName,qii.jcjgid,qcr.approveDate));
authid current_user
is
vn_ctn number;
begin
select count(*) into vn_ctn from user_all_tables a where a.table_name like upper('JDZMONTHTABLE');
if vn_ctn > 0 then
execute immediate 'drop table JDZMONTHTABLE';
end if;
execute immediate 'create table JDZMONTHTABLE as
select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,jc.districTid,qcr.approveDate,
count(*) as SAMPLEGROUPID,
sum(case when qcr.result in(''A'') then 1 else 0 end) "RESULT_A", --结论
sum(case when qcr.RESULT in(''B'') then 1 else 0 end) "RESULT_B",
sum(case when qcr.RESULT in(''D'') then 1 else 0 end) "RESULT_D",
sum(case when qcr.RESULT in(''C'') then 1 else 0 end) "RESULT_C",
sum(case when qcr.RESULT in(''E'') then 1 else 0 end) "RESULT_E",
sum(case when qcr.processstate in(''N'') then 1 else 0 end) "PROCESSSTATE",--曲线比对异常
sum(case when qcr.experimentationstate in(''N'') then 1 else 0 end) "EXPERIMENTATIONSTATE", --比对结果异常
sum(case when qcr.echosampleid is not null then 1 else 0 end) "ECHOSAMPLEID_TRUE", --复检的样品编号_是
sum(case when qcr.echosampleid is null then 1 else 0 end) "ECHOSAMPLEID_FALSE",--复检的样品编号_否
sum(case when qcr.realtimestate in(''N'') then 1 else 0 end) "REALTIMESTATE",--是否实时传输状态
sum(case when qcr.modifystate in(''Y'') then 1 else 0 end) "MODIFYSTATE", --数据被修改
sum(case when qcr.collectionstate in(''N'') then 1 else 0 end) "COLLECTIONSTATE" --未采集曲线数据
from report qcr
inner join compact jc on qcr.compactpid = jc.compactpid
inner join inspectinstitution qii on jc.districtid = qii.jcjgid
where qcr.approveDate=to_date(''2012-1'',''yyyy-mm'')
group by qii.inspectinstitutionpid,qii.inspectinstitutionName,jc.districTid,qcr.approveDate
)';
for p in 2..12
loop
if p<=12 then
insert into JDZMONTHTABLE(select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,jc.districTid,qcr.approveDate,
count(*) as SAMPLEGROUPID,
sum(case when qcr.result in('A') then 1 else 0 end) "RESULT_A", --结论
sum(case when qcr.RESULT in('B') then 1 else 0 end) "RESULT_B",
sum(case when qcr.RESULT in('D') then 1 else 0 end) "RESULT_D",
sum(case when qcr.RESULT in('C') then 1 else 0 end) "RESULT_C",
sum(case when qcr.RESULT in('E') then 1 else 0 end) "RESULT_E",
sum(case when qcr.processstate in('N') then 1 else 0 end) "PROCESSSTATE",--曲线比对异常
sum(case when qcr.experimentationstate in('N') then 1 else 0 end) "EXPERIMENTATIONSTATE", --比对结果异常
sum(case when qcr.echosampleid is not null then 1 else 0 end) "ECHOSAMPLEID_TRUE", --复检的样品编号_是
sum(case when qcr.echosampleid is null then 1 else 0 end) "ECHOSAMPLEID_FALSE",--复检的样品编号_否
sum(case when qcr.realtimestate in('N') then 1 else 0 end) "REALTIMESTATE",--是否实时传输状态
sum(case when qcr.modifystate in('Y') then 1 else 0 end) "MODIFYSTATE", --数据被修改
sum(case when qcr.collectionstate in('N') then 1 else 0 end) "COLLECTIONSTATE" --未采集曲线数据
from report qcr
inner join compact jc on qcr.compactpid = jc.compactpid
inner join inspectinstitution qii on jc.districtid = qii.jcjgid
where qcr.approveDate=to_date('2012-'||p||'','yyyy-mm')
group by qii.inspectinstitutionpid,qii.inspectinstitutionName,jc.districTid,qcr.approveDate
)
end if;
end loop;
exception
when others then
null;
end JDZMONTHCOUNT;
解决了