用Cognos做了一个报表,同样的条件第一次查询很快,第二次查询很慢,求大神帮忙解决。
sql:
select Case When c.handle_org_id In ('1944889','1944890','1944891') --海外公司
Then f_r_get_orgname_byid(c.handle_org_id)
Else f_r_get_commonname_byid(f_r_get_common_sheng(f_r_get_commonid_by_orgid(c.handle_org_id)))
End sheng ,
Case When c.handle_org_id In ('1944889','1944890','1944891','1944664','1944642','1944665','1944649') --海外公司和直辖市
Then Null
Else
case when f_r_get_commonname_byid(f_r_get_common_shi(f_r_get_commonid_by_orgid(c.handle_org_id))) is null then '--' else f_r_get_commonname_byid(f_r_get_common_shi(f_r_get_commonid_by_orgid(c.handle_org_id))) end
End shi ,
1 rent_reqnum,
Case when c.rent_oper_date is not null And trunc(c.rent_oper_date)<=f_r_get_date(c.finish_oper_date,to_number(#prompt('work_day','string')#),'1')
Then 1 Else 0 end rent_oper_js,
Case when c.rent_oper_date is null Or trunc(c.rent_oper_date)>f_r_get_date(c.finish_oper_date,to_number(#prompt('work_day','string')#),'1')
Then 1 Else 0 end rent_oper_cs,
f_r_get_displayname_by_fieldid('BUSI_TYPE',c.busi_type) busi_type,
c.order_item_id,
c.is_reqact_flag,
c.finish_oper_date
from tr_order_condition c
where c.order_state_cd In ('200000','300000','300098','401498')
-- 去除补单的登记事项 4040098061 2014-01-21
And c.apply_info Not In ('3020100000','4040100085','4040100009','4020100000','4040100090','4040098061')
and c.aud_order_date is not null
and c.product_id in ('10010073','10010066','10010022','10010064','10010021','10010019')
and c.handle_order_stage = '50030002'
And c.handle_org_id <> '1944640'
And c.finish_oper_date Is Not Null
and c.finish_oper_date >= f_r_get_date(TO_DATE(#prompt('start_time','string')#, 'yyyy-mm-dd'),-13,'1')
And c.finish_oper_date < f_r_get_date((TO_DATE(#prompt('end_time','string')#, 'yyyy-mm-dd') + 1),-to_number(#prompt('work_day','string')#),'1')
页面显示值: