一个oracleg与sql的问题
create or replace procedure xpro_refreshOrgChart(empType number) is
begin
delete xt_departmentrefresh;
if empType != 0 then
insert into xt_departmentrefresh
select substr(d.treeid, 0, length(d.treeid) - 2),
substr(d.treeid, 0, length(d.treeid) - 2),
d.treeid,
d.treeid,
d.departmentid,
dd.empcount || '人' || '/' || d.departmentname
from xdepartment d,
(select dd.treeid,sum(empCount) as empcount from xdepartment dd ,
(select d.treeid,count(e.employeeid) empCount
from xdepartment d , (select employeeid,departmentid,type1 from xemployee where lastworkdate is null or lastworkdate > sysdate) e
where e.departmentid (+)= d.departmentid and e.type1 = empType
group by d.treeid )ee where ee.treeid like dd.treeid||'%'
group by dd.treeid) dd
where d.treeid = dd.treeid;
else
insert into xt_departmentrefresh
select substr(d.treeid, 0, length(d.treeid) - 2),
substr(d.treeid, 0, length(d.treeid) - 2),
d.treeid,
d.treeid,
d.departmentid,
dd.empcount || '人' || '/' || d.departmentname
from xdepartment d,
(select dd.treeid,sum(empCount) as empcount from xdepartment dd ,
(select d.treeid,count(e.employeeid) empCount
from xdepartment d , (select employeeid,departmentid,type1 from xemployee where lastworkdate is null or lastworkdate > sysdate) e
where e.departmentid (+)= d.departmentid
group by d.treeid )ee where ee.treeid like dd.treeid||'%'
group by dd.treeid) dd
where d.treeid = dd.treeid;
end if;
end xpro_refreshOrgChart;
上面这个储存过程在oracle中是正确，如把转成sql sverver
问题点数：10、回复次数：2Top
1 楼wts173（Knight Figo）回复于 2006-07-05 20:18:26 得分 0
upTop
2 楼LouisXIV（夜游神）回复于 2006-07-05 20:25:41 得分 0
create procedure xpro_refreshOrgChart(@empType int)
as
truncate table xt_departmentrefresh
if @empType != 0
begin
insert into xt_departmentrefresh
select substring(d.treeid, 0, len(d.treeid) - 2),
substring(d.treeid, 0, len(d.treeid) - 2),
d.treeid,
d.treeid,
d.departmentid,
dd.empcount + '人' + '/' + d.departmentname
from xdepartment d,
(select dd.treeid,sum(empCount) as empcount from xdepartment dd ,
(select d.treeid,count(e.employeeid) empCount
from xdepartment d right join (select employeeid,departmentid,type1 from xemployee where lastworkdate is null or lastworkdate > sysdate) e
on e.departmentid = d.departmentid and e.type1 = @empType
group by d.treeid )ee where ee.treeid like dd.treeid+'%'
group by dd.treeid) dd
where d.treeid = dd.treeid
end
else
begin
insert into xt_departmentrefresh
select substring(d.treeid, 0, len(d.treeid) - 2),
substring(d.treeid, 0, len(d.treeid) - 2),
d.treeid,
d.treeid,
d.departmentid,
dd.empcount + '人' + '/' + d.departmentname
from xdepartment d,
(select dd.treeid,sum(empCount) as empcount from xdepartment dd ,
(select d.treeid,count(e.employeeid) empCount
from xdepartment d right join (select employeeid,departmentid,type1 from xemployee where lastworkdate is null or lastworkdate > sysdate) e
on e.departmentid = d.departmentid
group by d.treeid )ee where ee.treeid like dd.treeid+'%'
group by dd.treeid) dd
where d.treeid = dd.treeid
endTop
