遇到一问题 我这一条sql语句中 用with建了很多临时表所以这条sql语句 比较长,但这条sql语句在plsql中运行没有问题,但在项目运行过程中报错“求助:ORA-32036: 不支持 WITH 子句中串联式查询名的形式”。ExecuteDataSet(CommandType.Text, sql)用的就是这个方法。哪位大侠可帮忙?(下面是sql语句)with temp as
( select distinct log.doccode,1 counts, log.organname,log.ispunctual, isprecise isprecise ,sue.isagain AgainCount,sue.isend from
flowprocesslog log inner join sue on log.doccode = sue.workflowinstance
where log.stepcode = '51f56286-631d-403d-82eb-f35d29025a5e' and sue.createdate >= to_date('2012-3-5 0:00:01','yyyy-MM-dd hh24:mi:ss')
and sue.createdate <= to_date('2012-3-9 0:00:01','yyyy-MM-dd hh24:mi:ss') ), temp1 as ( select distinct ass.workflowinstance,1 counts,
organ.organname,0 ispunctual,0 isprecise, 0 AgainCount, 0 isend from taskassignment ass inner join workflowinstance inst on
ass.workflowinstance = inst.id inner join users on ass.actor = users.loginname inner join person on users.personid = person.personid
inner join organ on person.organcode = organ.organcode inner join sue on sue.workflowinstance = inst.id where
inst.state = '51f56286-631d-403d-82eb-f35d29025a5e' and sue.createdate >= to_date('2012-3-5 0:00:01','yyyy-MM-dd hh24:mi:ss')
and sue.createdate <= to_date('2012-3-9 0:00:01','yyyy-MM-dd hh24:mi:ss') union select * from temp ),
temp2 as
( select organ.organname,sueback.workflowinstance,count(effect) BackCount from sueback inner join organ on sueback.organcode = organ.organcode
right join temp1 on sueback.workflowinstance = temp1.workflowinstance where sueback.effect = '0' group by organ.organname,sueback.workflowinstance ),
temp3 as
( select temp1.*,temp2.BackCount from temp1 left join temp2 on temp1.workflowinstance = temp2.workflowinstance and
temp1.organname = temp2.organname), temp4 as ( select organ.organname,count(sueapply.applycode) applyCount from sueapply inner join
temp1 on temp1.workflowinstance = sueapply.workflowinstance inner join organ on sueapply.applyorgancode = organ.organcode where
sueapply.iseffect = '0' group by organ.organname ), temp5 as ( select case when sum(counts) is null then 0 else sum(counts) end
as Counts, case when sum(ispunctual) is null then 0 else sum(ispunctual) end as PunctualCount ,
case when sum(isprecise) is null then 0 else sum(isprecise) end as PreciseCount, case when sum(AgainCount) is null then 0 else sum(AgainCount) end as AgainCount,
case when sum(BackCount) is null then 0 else sum(BackCount) end as BackCount , case when sum(isend) is null then 0 else sum(isend)
end as EndCount, case when sum(temp4.applyCount) is null then 0 else sum(temp4.applyCount) end as ProlongCount,
organ.organname from organ left join temp3 on organ.organname = temp3.organname left join temp4 on organ.organname = temp4.organname
group by organ.organname order by counts desc ),
temp6 as ( select organ.organname,count(temp222.doccode) ProcessedCount from
organ left join temp temp222 on organ.organname = temp222.organname group by organ.organname ),
temp7 as ( select organ.organname, count(temp1.workflowinstance) IntimeEndCount from organ left join temp1 on
organ.organname = temp1.organname where temp1.ispunctual = '1' and isend = '1' group by organ.organname )
select temp5.*,temp6.ProcessedCount,case when temp7.IntimeEndCount is null then 0 else temp7.IntimeEndCount end
as IntimeEndCount from temp5 inner join temp6 on temp5.organname = temp6.organname left join temp7 on
temp5.organname = temp7.organname order by temp5.counts desc
( select distinct log.doccode,1 counts, log.organname,log.ispunctual, isprecise isprecise ,sue.isagain AgainCount,sue.isend from
flowprocesslog log inner join sue on log.doccode = sue.workflowinstance
where log.stepcode = '51f56286-631d-403d-82eb-f35d29025a5e' and sue.createdate >= to_date('2012-3-5 0:00:01','yyyy-MM-dd hh24:mi:ss')
and sue.createdate <= to_date('2012-3-9 0:00:01','yyyy-MM-dd hh24:mi:ss') ), temp1 as ( select distinct ass.workflowinstance,1 counts,
organ.organname,0 ispunctual,0 isprecise, 0 AgainCount, 0 isend from taskassignment ass inner join workflowinstance inst on
ass.workflowinstance = inst.id inner join users on ass.actor = users.loginname inner join person on users.personid = person.personid
inner join organ on person.organcode = organ.organcode inner join sue on sue.workflowinstance = inst.id where
inst.state = '51f56286-631d-403d-82eb-f35d29025a5e' and sue.createdate >= to_date('2012-3-5 0:00:01','yyyy-MM-dd hh24:mi:ss')
and sue.createdate <= to_date('2012-3-9 0:00:01','yyyy-MM-dd hh24:mi:ss') union select * from temp ),
temp2 as
( select organ.organname,sueback.workflowinstance,count(effect) BackCount from sueback inner join organ on sueback.organcode = organ.organcode
right join temp1 on sueback.workflowinstance = temp1.workflowinstance where sueback.effect = '0' group by organ.organname,sueback.workflowinstance ),
temp3 as
( select temp1.*,temp2.BackCount from temp1 left join temp2 on temp1.workflowinstance = temp2.workflowinstance and
temp1.organname = temp2.organname), temp4 as ( select organ.organname,count(sueapply.applycode) applyCount from sueapply inner join
temp1 on temp1.workflowinstance = sueapply.workflowinstance inner join organ on sueapply.applyorgancode = organ.organcode where
sueapply.iseffect = '0' group by organ.organname ), temp5 as ( select case when sum(counts) is null then 0 else sum(counts) end
as Counts, case when sum(ispunctual) is null then 0 else sum(ispunctual) end as PunctualCount ,
case when sum(isprecise) is null then 0 else sum(isprecise) end as PreciseCount, case when sum(AgainCount) is null then 0 else sum(AgainCount) end as AgainCount,
case when sum(BackCount) is null then 0 else sum(BackCount) end as BackCount , case when sum(isend) is null then 0 else sum(isend)
end as EndCount, case when sum(temp4.applyCount) is null then 0 else sum(temp4.applyCount) end as ProlongCount,
organ.organname from organ left join temp3 on organ.organname = temp3.organname left join temp4 on organ.organname = temp4.organname
group by organ.organname order by counts desc ),
temp6 as ( select organ.organname,count(temp222.doccode) ProcessedCount from
organ left join temp temp222 on organ.organname = temp222.organname group by organ.organname ),
temp7 as ( select organ.organname, count(temp1.workflowinstance) IntimeEndCount from organ left join temp1 on
organ.organname = temp1.organname where temp1.ispunctual = '1' and isend = '1' group by organ.organname )
select temp5.*,temp6.ProcessedCount,case when temp7.IntimeEndCount is null then 0 else temp7.IntimeEndCount end
as IntimeEndCount from temp5 inner join temp6 on temp5.organname = temp6.organname left join temp7 on
temp5.organname = temp7.organname order by temp5.counts desc
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货