sql - PLSQL Total 字段的问题 - 不计算

Question

我正在尝试从 Oracle PLSQL 中的 CTE（视图内）计算 SELECT 语句中的 3 个字段。问题是该字段是创建的，但是它是空白的，即不计算。脚本的其余部分工作正常。唯一的问题是总字段（称为totalll）。这是完整的代码。还附上了一张结果的图片。想知道 PLSQL 中是否有任何限制，因为这是 View 中的 CTE。

create or replace view INVENTORY as
with
instock as
(select part_no, INV_PART_API.Get_Desc(contract,part_no) part_description, location_no, qty_onhand, qty_reserved,
(qty_onhand - qty_reserved) available_qty, objversion
from INVENTORY_PART_IN_STOCK
where location_no IN ('A','B','C', 'D') and qty_onhand > 0 ),
intransit as
(select part_no, 
shaw1app.INV_PART_API.Get_Desc(contract,part_no) part_description, 
location_no, 
qty_onhand On_Hand_In_Transit, 
qty_reserved Reserved_In_Transit,
(qty_onhand - qty_reserved) Available_Qty_In_Transit, objversion
from INVENTORY_PART_IN_STOCK
where location_no IN ('E') and qty_onhand > 0 ),
inprod as 
(select part_no, description, 
buy_qty_due Qty_in_Production, objversion from PUR_ORDER_LINE_ALL
where objstate in ('Confirmed', 'Released') and demand_code_db = 'Invent Order' and
vendor_no = '11111'),
quarantine as
(select part_no, INV_PART_API.Get_Desc(contract,part_no), location_no, qty_onhand OnHandQuar, qty_reserved ReservedQuarantine,
(qty_onhand - qty_reserved) Available_Qty_Quar

from INVENTORY_PART_IN_STOCK
where location_no IN ('N-QA', 'N8-QA') and qty_onhand > 0)


select s.part_no, s.part_description, s.location_no, s.qty_onhand, s.qty_reserved,s.available_qty,
t.On_Hand_In_Transit,
t.Reserved_In_Transit,
t.Available_Qty_In_Transit,
q.OnHandQuar, q.ReservedQuarantine, q.Available_Qty_Quar,
p.Qty_in_Production, (s.available_qty + t.Available_Qty_In_Transit + p.Qty_in_Production ) totalll
from
instock s
left join intransit t on t.part_no = s.part_no
left join inprod p on p.part_no = s.part_no
left join quarantine q on q.part_no = s.part_no

with read only;

让我知道是否需要进一步解释。

谢谢！

标签： sqlselectplsql

Answer 1

我认为添加没有或应该有任何限制。

但是我从提供的代码中看到的是你正在做 3 个字段的总和来获得totalllas

(s.available_qty + t.available_qty_in_transit + p.qty_in_production) totalll

并且在您提供的示例输出中，只有在s.available_qtywhere 和其他字段中可用的值t.available_qty_in_transit和p.qty_in_productionare为 null 导致最终结果也为 null。任何涉及 null 的算术运算都将是最终结果 null。

将其更改为

(nvl(s.available_qty,0) + nvl(t.available_qty_in_transit,0) + nvl(p.qty_in_production,0)) totalll

应该解决问题。