运行代码报错，独立运行部分正常
求助大大们，我的代码那里错了，如果不用临时的数据库转存储，直接运行一切正常。
select
ord_id, ord_no, ord_ext1, ord_title, ord_date, cu_depart, jb_creator, jb_type,jb_enddate, ord_depart,
min(rtrim(str(jb_id))+jb_context) as reason, ord_status,
/* 计算本业务单据【返回】和【被返回】合计数量，其中在计算【被返回】总数时会剔除【总经办审核步骤】的【被返回】数据，而【返回】计算全部数据 */
(select count(jb_id) from mps_job where jb_ordid=ord_id and jb_result1='{ed_fhzt}' and (('{ed_fhzt}' = case when jb_type not like '%总经办%' then '被返回' else '未知' end) or ('{ed_fhzt}' = case when jb_type not like '%未知%' then '返回' else '未知' end))) as retry,
/* 计算业务最后完成那步骤的时间 */
(select max(jb_enddate) from mps_job where jb_ordid=ord_id) as ord_date00,
/* 计算业务开始时间到最后步骤时间，中途所用的天数 */
(select datediff(dd,ord_date,max(jb_enddate)) from mps_job where jb_ordid=ord_id)+1 as auditdays
from
mps_job, mps_business, mps_crmworks, mps_compuser
where
jb_ordid=ord_id and
jb_wrkid=wrk_id and
jb_createcuid=cu_id and
jb_time>0.1 and
ord_crmid={ddp_type} and
('{ed_fqbm}'='' or ',{ed_fqbm},' like '%,'+ord_depart+',%') and
('{ed_ywbh}'='' or ord_no like '%{ed_ywbh}%') and
ord_no not like '%(临)%' and
('{ed_ywlx}'='' or ',{ed_ywlx},' like '%,'+ord_ext1+',%') and
('{ed_bhbm}'='' or ',{ed_bhbm},' like '%,'+cu_depart+',%') and
('{ed_bbhbm}'='' or ',{ed_bbhbm},' not like '%,'+cu_depart+',%') and
ord_date>='{ed_date1} 00:00:00' and
ord_date<='{ed_date2} 23:59:59' and
/* 完成业务评审的取得业务完成审核的时间。如果业务在评审中，那么就是评审最后一步所用的时间 */
(select max(jb_enddate) from mps_job where jb_ordid=ord_id)>='{ed_enddate1} 00:00:00' and
jb_result1='{ed_fhzt}' and
(('{ed_xmzt}' = case when ord_status='立项已完成' or ord_status='变更已完成' then '评审已通过'
else '未知' end) or
('{ed_xmzt}' = case when ord_status<>'立项已完成' and ord_status<>'变更已完成' then '评审未通过'
else '未知' end) or
('{ed_xmzt}' = case when ord_status<>'全部' then '全部'
else '未知' end))
group by
ord_type, ord_no, ord_ext1, ord_id, ord_title, ord_date, cu_depart, jb_creator, jb_type, jb_enddate,
ord_status, ord_depart
having
count(jb_id)>0
order by
ord_type, ord_no
create table #xmxtkh(kh_ord_id bigint, kh_ord_no varchar(50), kh_ord_ext1 nvarchar(100), kh_ord_title nvarchar(100),
kh_ord_date datetime, kh_ord_depart varchar(30), kh_jp_creator nvarchar(50), kh_jp_type nvarchar(50),
kh_jp_enddate datetime, kh_ord_depart varchar(30), kh_ord_status nvarchar(16), kh_reason nvarchar(500),
kh_count bigint, kh_ord_date00 datetime, kh_ayditdays bigint)
insert into #xmxtkh(kh_ord_id, kh_ord_no, kh_ord_ext1, kh_ord_title, kh_ord_date, kh_ord_depart, kh_jp_creator,
kh_jp_type, kh_jp_enddate, kh_ord_depart, kh_ord_status, kh_reason, kh_count, kh_ord_date00, kh_ayditdays)
select
ord_id, ord_no, ord_ext1, ord_title, ord_date, cu_depart, jb_creator, jb_type, jb_enddate, ord_depart, ord_status,
min(rtrim(str(jb_id))+jb_context),
/* 计算本业务单据【返回】和【被返回】合计数量，其中在计算【被返回】总数时会剔除【总经办审核步骤】的【被返回】数据，而【返回】计算全部数据 */
(select count(jb_id) from mps_job where jb_ordid=ord_id and jb_result1='{ed_fhzt}' and (('{ed_fhzt}' = case when jb_type not like '%总经办%' then '被返回' else '未知' end) or ('{ed_fhzt}' = case when jb_type not like '%未知%' then '返回' else '未知' end))),
/* 计算业务最后完成那步骤的时间 */
(select max(jb_enddate) from mps_job where jb_ordid=ord_id),
/* 计算业务开始时间到最后步骤时间，中途所用的天数 */
(select datediff(dd,ord_date,max(jb_enddate)) from mps_job where jb_ordid=ord_id)+1
from
mps_job, mps_business, mps_crmworks, mps_compuser
where
jb_ordid=ord_id and
jb_wrkid=wrk_id and
jb_createcuid=cu_id and
jb_time>0.1 and
ord_crmid={ddp_type} and
('{ed_fqbm}'='' or ',{ed_fqbm},' like '%,'+ord_depart+',%') and
('{ed_ywbh}'='' or ord_no like '%{ed_ywbh}%') and
ord_no not like '%(临)%' and
('{ed_ywlx}'='' or ',{ed_ywlx},' like '%,'+ord_ext1+',%') and
('{ed_bhbm}'='' or ',{ed_bhbm},' like '%,'+cu_depart+',%') and
('{ed_bbhbm}'='' or ',{ed_bbhbm},' not like '%,'+cu_depart+',%') and
ord_date>='{ed_date1} 00:00:00' and
ord_date<='{ed_date2} 23:59:59' and
/* 完成业务评审的取得业务完成审核的时间。如果业务在评审中，那么就是评审最后一步所用的时间 */
(select max(jb_enddate) from mps_job where jb_ordid=ord_id)>='{ed_enddate1} 00:00:00' and
jb_result1='{ed_fhzt}' and
(('{ed_xmzt}' = case when ord_status='立项已完成' or ord_status='变更已完成' then '评审已通过'
else '未知' end) or
('{ed_xmzt}' = case when ord_status<>'立项已完成' and ord_status<>'变更已完成' then '评审未通过'
else '未知' end) or
('{ed_xmzt}' = case when ord_status<>'全部' then '全部'
else '未知' end))
group by
ord_type, ord_no, ord_ext1, ord_id, ord_title, ord_date, cu_depart, jb_creator, jb_type, jb_enddate,
ord_status, ord_depart
having
count(jb_id)>0
order by
ord_no
select
kh_ord_id, kh_ord_no, kh_ord_ext1, kh_ord_title, kh_ord_date, kh_ord_depart, kh_jp_creator,
kh_jp_type, kh_jp_enddate, kh_ord_depart, kh_ord_status, kh_reason, kh_count, kh_ord_date00, kh_ayditdays
from
mps_job, #xmxtkh
where
jb_ordid=kh_ord_id and
jb_ordno=kh_ord_no and
jb_date=kh_jp_enddate and
order by
ord_no
IF OBJECT_ID('tempdb..#a') IS NOT NULL DROP TABLE #a
CREATE TABLE #a (id INT)
