如:
--存储过程
create procedure REPORTVIEWER_count_type_proc
(@year int,@month int,@daty int)
as
begin
select y_num_e,m_num_e,d_num_e,m_num_s,y_num_zf,m_num_zf,r_title from
(
--年-完成
select distinct a.r_title,b.rdlc_num y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--月-完成
select distinct a.r_title,null y_num_e,b.rdlc_num m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--日-完成
select distinct a.r_title,null y_num_e,null m_num_e,b.rdlc_num d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'dd')=@daty group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--作业中
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,b.rdlc_num m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='申请'
union all
--年-作废
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,b.rdlc_num y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='作废'
union all
--月-作废
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,b.rdlc_num m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='作废'
) as proc_REPORTVIEWER_count_type
end
--存储过程
create procedure REPORTVIEWER_count_type_proc
(@year int,@month int,@daty int)
as
begin
select y_num_e,m_num_e,d_num_e,m_num_s,y_num_zf,m_num_zf,r_title from
(
--年-完成
select distinct a.r_title,b.rdlc_num y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--月-完成
select distinct a.r_title,null y_num_e,b.rdlc_num m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--日-完成
select distinct a.r_title,null y_num_e,null m_num_e,b.rdlc_num d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'dd')=@daty group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--作业中
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,b.rdlc_num m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='申请'
union all
--年-作废
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,b.rdlc_num y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='作废'
union all
--月-作废
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,b.rdlc_num m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='作废'
) as proc_REPORTVIEWER_count_type
end
报错 改怎么办?
还有就是execute REPORTVIEWER_count_type_proc 2013,12,26报错无效SQL语句
那么视图view_REPORTVIEWER_count_type中不是有按年,月,日为判断条件的吗?那么以年为判断条件的取select * from view_REPORTVIEWER_count_type(视图) where 年=...and月=...and日=...中年的值,以月为判断条件的取select * from view_REPORTVIEWER_count_type(视图) where 年=...and月=...and日=...中月的值,以日为判断条件的取select * from view_REPORTVIEWER_count_type(视图) where 年=...and月=...and日=...中日的值
这个我还没弄过呢你试试更改视图结构
更改视图结构?好像没得改 六条SQL就只有其中两个where条件不同 现在想做的就是在视图外做where时间的判断 但是要针对视图内六条SQL语句 这点过不去
你这样写就行的
好像没啥变化啊?是没有变化,你执行这个语句,会报错不
不会
但是execute REPORTVIEWER_count_type_proc 2013,12,26这个会报错 无效SQL语句
版主 存储过程我也写了 你看看
execute REPORTVIEWER_count_type_proc 2013,12,26报错 无效SQL语句
而且存储过程好像不能
select * from REPORTVIEWER_count_type_proc where 年=... and 月=... and 日=...
我最终还是要转成select .....的
exec REPORTVIEWER_count_type_proc 参数
然后操作xxx表
你这个语句是在oracle中建的把
这个我还没弄过呢你试试更改视图结构
更改视图结构?好像没得改 六条SQL就只有其中两个where条件不同 现在想做的就是在视图外做where时间的判断 但是要针对视图内六条SQL语句 这点过不去那就转用存储过程吧,不要用视图了。
嗯嗯
嗯,因为存储过程返回一个结果集,但是要找个地方存起来才能操作,那个XXX可以是临时表,
create table proc_REPORTVIEWER_count_type
(
r_title varchar(20),
y_num_e number,
m_num_e number,
d_num_e number,
m_num_s number,
y_num_zf number,
m_num_zf number
)insert into proc_REPORTVIEWER_count_type
execute values REPORTVIEWER_count_type_proc 报错 未实施的功能
create table xxx(....)
insert into xxx
exec proc xxxxxx
select *
from xxx
这样的,oracle小弟不懂
execute 和 REPORTVIEWER_count_type_proc 之间 加了也错
exec xxx这样的格式试试