已开启行式引擎分页sql如下SELECT *
FROM (SELECT *
FROM (
select burks_id, burks_name, piv_hyk_no, psl_pro_id, psl_pro_name, zgg, zsccj, dl_name, zl_name, xl_name, month1, month2, month3, month4, month5, month6, month7, month8, month9, month10, month11, month12,'${p_month}' p_month,
month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12 sumallmonth,
case when used_days = 0 then 0 else 30/used_days end monthusedqty,
case when used_days = 0 then 0 else 30/used_days end * to_number(right('${p_month}',2)) allusedqty,
gk_flag,
(month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12) - (case when used_days = 0 then 0 else 30/used_days end * to_number(right('${p_month}',2))) dot,
case when maxpslmonth = '${p_month}' then '是' else '否' end isbuy,
case when gk_flag = '是' then '公卡'
when gk_flag = '否' then
case when (month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12) - (case when used_days = 0 then 0 else 30/used_days end * to_number(right('${p_month}',2))) >=0
and maxpslmonth = '${p_month}' then '优'
when (month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12) - (case when used_days = 0 then 0 else 30/used_days end * to_number(right('${p_month}',2))) >=0
and maxpslmonth != '${p_month}' then '高'
when (month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12) - (case when used_days = 0 then 0 else 30/used_days end * to_number(right('${p_month}',2))) <0
and maxpslmonth = '${p_month}' then '中'
when (month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12) - (case when used_days = 0 then 0 else 30/used_days end * to_number(right('${p_month}',2))) <0
and maxpslmonth != '${p_month}' then '低' end
end hybq,
to_number(right(left(trunc(sysdate()),7),2))-to_number(right(maxmonth,2)) deltmonth
from (select p.burks_id,p.burks_name,p.piv_hyk_no,p.psl_pro_id,p.psl_pro_name,p.zgg,p.zsccj,p.dl_name,p.dl_id,p.zl_name,p.zl_id,p.xl_name,p.xl_id,p.gk_flag,nvl(p.used_days,0) used_days,max(p.last_buy_month) maxmonth,max(p.psl_month) maxpslmonth,
sum(case when right(p.psl_month,2) = '01' then p.psl_qty else 0 end) month1,
sum(case when right(p.psl_month,2) = '02' then p.psl_qty else 0 end) month2,
sum(case when right(p.psl_month,2) = '03' then p.psl_qty else 0 end) month3,
sum(case when right(p.psl_month,2) = '04' then p.psl_qty else 0 end) month4,
sum(case when right(p.psl_month,2) = '05' then p.psl_qty else 0 end) month5,
sum(case when right(p.psl_month,2) = '06' then p.psl_qty else 0 end) month6,
sum(case when right(p.psl_month,2) = '07' then p.psl_qty else 0 end) month7,
sum(case when right(p.psl_month,2) = '08' then p.psl_qty else 0 end) month8,
sum(case when right(p.psl_month,2) = '09' then p.psl_qty else 0 end) month9,
sum(case when right(p.psl_month,2) = '10' then p.psl_qty else 0 end) month10,
sum(case when right(p.psl_month,2) = '11' then p.psl_qty else 0 end) month11,
sum(case when right(p.psl_month,2) = '12' then p.psl_qty else 0 end) month12
from dm_rh.dm_hy_sku_dot_sale p
group by p.burks_id,p.burks_name,p.piv_hyk_no,p.psl_pro_id,p.psl_pro_name,p.zgg,p.zsccj,p.dl_name,p.zl_name,p.xl_name,p.gk_flag,p.used_days,p.dl_id,p.zl_id,p.xl_id) a1
order by (burks_id,burks_name,piv_hyk_no,psl_pro_id) ASC
limit ${fr_pagesize*fr_pagenumber}) AS e1
ORDER BY (e1.burks_id,e1.burks_name,e1.piv_hyk_no,e1.psl_pro_id) DESC
limit ${if(fr_pagenumber == int((((fr_rowcount-1)/fr_pagesize)+1)),fr_rowcount - (fr_pagesize*(fr_pagenumber-1)),fr_pagesize)}) AS e2
ORDER BY (e2.burks_id,e2.burks_name,e2.piv_hyk_no,e2.psl_pro_id) ASC报错如下
