SQL无效标识查询
问题描述:
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是一个无效的标识错误,而且我不确定我是否会与其他标识符得到它的。任何光线都会让人惊叹。因为我不明白为什么它与其他人不同。SQL无效标识查询
答
你不能在它产生相同select
条款指的alias
名。
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
加入哪个更可读
+0
我已经修改了原来的职位代码为我编辑了以下的评论。感谢您的帮助,因为我可以看到我出错的地方。不知道我是否可以像我一样嵌套子查询。 –
标签您正在使用的数据库管理系统。该代码是特定于产品的。 – jarlh
没有人会通过这个烂摊子进行筛选。很可能是一个小错误,但你需要自己阐明这一点。 –