问题代码 :

select nvl (case when g.munit=’KG’ then count(g.code)
when g.munit=’LB’ then count(g.code)
else sum(b2.qty) end, 0) sales_qty , g.code gcode, s.code scode
— into v_sales_qty1
from stos_buy2 b2
inner join stos_buy1 b1 on b1.flowno=b2.flowno and b1.posno=b2.posno
join orggoods g on b2.gid=g.gid –and g.orggid= {$currentOrg}
–join sort s on s.code=left(g.sort,2)
join store s on s.code=b1.store_code
where 1=1
/* and s.code = nvl(instore, s.code)
and g.code =nvl(ingcode , g.code)
and trunc(lg.time)>=nvl(inbgdate,trunc(lg.time))
and trunc(lg.time)>=nvl(ineddate,trunc(lg.time))*/
and date(b1.tran_date)>=nvl( ‘2022-04-29’,date(b1.tran_date) )
and date(b1.tran_date)<=nvl( ‘2022-04-30’,date(b1.tran_date) )
and s.code = nvl(‘1105’, s.code)
and g.code =nvl(‘10076088’ , g.code)
and g.code not in (‘-‘ , ”)
and g.dep in (’11’,’12’)
and g.code is not null
group by g.code , s.code, g.munit

改为:

select nvl(s.sales_qty,0) , qid.query_id
from
(values (1)) as qid (query_id)
left join (
select nvl (case when g.munit=’KG’ then count(g.code)
when g.munit=’LB’ then count(g.code)
else sum(b2.qty) end, 0) sales_qty , g.code gcode, s.code scode
— into v_sales_qty1
from stos_buy2 b2
inner join stos_buy1 b1 on b1.flowno=b2.flowno and b1.posno=b2.posno
join orggoods g on b2.gid=g.gid –and g.orggid= {$currentOrg}
–join sort s on s.code=left(g.sort,2)
join store s on s.code=b1.store_code
where 1=1
/* and s.code = nvl(instore, s.code)
and g.code =nvl(ingcode , g.code)
and trunc(lg.time)>=nvl(inbgdate,trunc(lg.time))
and trunc(lg.time)>=nvl(ineddate,trunc(lg.time))*/
and date(b1.tran_date)>=nvl( ‘2022-04-29’,date(b1.tran_date) )
and date(b1.tran_date)<=nvl( ‘2022-04-30’,date(b1.tran_date) )
and s.code = nvl(‘1105’, s.code)
and g.code =nvl(‘10076088’ , g.code)
and g.code not in (‘-‘ , ”)
and g.dep in (’11’,’12’)
and g.code is not null
group by g.code , s.code, g.munit) s
on
s.scode = nvl(‘1105’, s.gcode)
and s.gcode =nvl(‘10076088’ , s.gcode)

Leave a Reply

Your email address will not be published.