select x_pick.prod_code,
x_order.order_state,
x_pick.pick_state,
x_pick.pick_qty,
x_pick.pick_location,
x_order.wave_id,
x_prod.normal_pick_area,
x_apr_loc.location,
x_order.reqd_despatch_time,
x_prod.bncd_awkward_item,
master.loc_count
from x_pick,
x_order,
x_prod,
x_apr_loc,
-- True Qty--
(
select
sub1.loc_count,
area.normal_pick_area,
(case when SUM(aerosolqty) = 0 then 0 when SUM(aerosolvisit) = 0 then 0 else SUM(aerosolqty) / SUM(aerosolvisit) end) as aerosolratio,
(case when SUM(hazqty) = 0 then 0 when SUM(hazvisit) = 0 then 0 else SUM(hazqty) / SUM(hazvisit) end) as hazratio,
(case when SUM(hvqty) = 0 then 0 when SUM(hvvisit) = 0 then 0 else SUM(hvqty) / SUM(hvvisit) end) as hvratio,
(case when SUM(hlopqty) = 0 then 0 when SUM(hlopvisit) = 0 then 0 else SUM(hlopqty) / SUM(hlopvisit) end) as hlopratio,
(case when SUM(plqty) = 0 then 0 when SUM(plvisit) = 0 then 0 else SUM(plqty) / SUM(plvisit) end) as plratio,
(case when SUM(miniloadqty) = 0 then 0 when SUM(miniloadvisit) = 0 then 0 else SUM(miniloadqty) / SUM(miniloadvisit) end) as miniloadratio,
(case when SUM(aprqty) = 0 then 0 when SUM(aprvisit) = 0 then 0 else SUM(aprqty) / SUM(aprvisit) end) as aprratio,
(case when SUM(apr2qty) = 0 then 0 when SUM(apr2visit) = 0 then 0 else SUM(apr2qty) / SUM(apr2visit) end) as apr2ratio,
(case when SUM(aprawkqty) = 0 then 0 when SUM(aprawkvisit) = 0 then 0 else SUM(aprawkqty) / SUM(aprawkvisit) end) as aprawkratio,
(SUM(aerosolqty + hazqty + hvqty + hlopqty + plqty + miniloadqty + aprqty + apr2qty + aprawkqty)) as grandtotal,
(SUM(aerosolvisit + hazvisit + hvvisit + hlopvisit + plvisit + miniloadvisit + aprvisit + apr2visit + aprawkvisit)) as grandvisit
from
(select area.normal_pick_area,
(case when area.normal_pick_area = 'Aerosol' then trueqty else 0 end) as aerosolqty,
(case when area.normal_pick_area = 'Aerosol' then truevisits else 0 end) as aerosolvisit,
(case when area.normal_pick_area = 'Haz Shed' then trueqty else 0 end) as hazqty,
(case when area.normal_pick_area = 'Haz Shed' then truevisits else 0 end) as hazvisit,
(case when area.normal_pick_area = 'High Value' then trueqty else 0 end) as hvqty,
(case when area.normal_pick_area = 'High Value' then truevisits else 0 end) as hvvisit,
(case when area.normal_pick_area = 'HLOP' then trueqty else 0 end) as hlopqty,
(case when area.normal_pick_area = 'HLOP' then truevisits else 0 end) as hlopvisit,
(case when area.normal_pick_area = 'Pallet Live' then trueqty else 0 end) as plqty,
(case when area.normal_pick_area = 'Pallet Live' then truevisits else 0 end) as plvisit,
(case when area.normal_pick_area = 'APR' then trueqty else 0 end) as aprqty,
(case when area.normal_pick_area = 'APR' then truevisits else 0 end) as aprvisit,
(case when area.normal_pick_area = 'Miniload' then trueqty else 0 end) as miniloadqty,
(case when area.normal_pick_area = 'Miniload' then truevisits else 0 end) as miniloadvisit,
(case when area.normal_pick_area = 'APR 2' then trueqty else 0 end) as apr2qty,
(case when area.normal_pick_area = 'APR 2' then truevisits else 0 end) as apr2visit,
(case when area.normal_pick_area = 'APR Awkward' then trueqty else 0 end) as aprawkqty,
(case when area.normal_pick_area = 'APR Awkward' then truevisits else 0 end) as aprawkvisit
from (
select
x_prod.normal_pick_area,
x_pick.pick_location,
(
case when x_prod.normal_pick_area = 'TP' then 'Miniload' when x_prod.normal_pick_area = 'MTP' then 'Miniload' when x_prod.normal_pick_area = 'PL' then 'Pallet Live' when x_prod.normal_pick_area = 'HLOP' then 'HLOP' when x_prod.normal_pick_area = 'STILLAGE' then 'High Value' when x_prod.normal_pick_area = 'ZA' then 'Aerosol' when x_prod.normal_pick_area = 'ZH' then 'Haz Shed' when x_prod.normal_pick_area = 'APR'
and Substr(x_pick.pick_location, 3, 2) < '22'
and x_prod.bncd_awkward_item = 'YES' then 'APR Awkward' when x_prod.normal_pick_area = 'APR'
and Substr(x_pick.pick_location, 3, 2) < '22'
and x_prod.bncd_awkward_item = 'NO' then 'APR' when x_prod.normal_pick_area = 'APR'
and Substr(x_pick.pick_location, 3, 2) > '21'
and x_prod.bncd_awkward_item = 'NO' then 'APR 2' when x_prod.normal_pick_area = 'APR'
and Substr(x_pick.pick_location, 3, 2) > '21'
and x_prod.bncd_awkward_item = 'YES' then 'TD1' else 'Unknown' end
)
from x_pick,x_prod) area, qtyvisit,
((select sub1.loc_count,x_pick.pick_qty,
(case when sub1.loc_count < 2 then x_pick.pick_qty else x_pick.pick_qty / sub1.loc_count end) as trueqty,
(case when sub1.loc_count < 2 then 1 else 1 / sub1.loc_count end) as truevisits
from x_pick,sub1) truev from
(
select
x_pick.prod_code,
area.normal_pick_area,
Count(distinct (x_apr_loc.location)) as loc_count
from
x_apr_loc,
x_pick
where
x_apr_loc.prod_code(+) = x_pick.prod_code
group by
x_pick.prod_code)) sub1)
x_pick)
) master
where x_pick.prod_code(+) = sub1.prod_code
and x_pick.prod_code = x_apr_loc.prod_code(+)
and x_pick.prod_code = x_prod.prod_code
and x_pick.order_id = x_order.order_id
and x_pick.pick_state in (
'RESERVED',
'WAIT REPLEN',
'STARTED'
)
and x_order.order_state in (
'RESERVED',
'STARTED'
)
你好，我收到一个错误，说grandvisit 是一个无效的标识符，我不确定我是否也会用其他标识符来获取它。对此的任何启示都会是惊人的。因为我不明白为什么它与其他人不同。
您不能alias在select生成它的同一子句中引用名称。
SELECT Sum(aerosolvisit + hazvisit + hvvisit + hlopvisit
+ plvisit + miniloadvisit + aprvisit + apr2visit
+ aprawkvisit) AS grandvisit
要计算grandratio您正在使用以下使用别名的表达式grandvisit
SELECT CASE
WHEN Sum(grandtotal) = 0 THEN 0
WHEN Sum(grandvisit) = 0 THEN 0
ELSE Sum(grandtotal) / Sum(grandvisit)
END AS grandratio
您可以使用派生表来执行此操作
SELECT CASE
WHEN Sum(grandtotal) = 0 THEN 0
WHEN Sum(grandvisit) = 0 THEN 0
ELSE Sum(grandtotal) / Sum(grandvisit)
END AS grandratio
From
(
SELECT Sum(aerosolvisit + hazvisit + hvvisit + hlopvisit
+ plvisit + miniloadvisit + aprvisit + apr2visit
+ aprawkvisit) AS grandvisit
...
) A
注意：您使用的是旧式外连接的( +=)。最好使用Right/Left更具可读性的连接
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句