组合来自3个独立SELECT语句(PLSQL)的结果/数据

Question

我正在尝试组合来自3个SELECT语句的数据。第一个是显示"；现有库存"；第二个-"；"运输中的货物"；第三个-"；生产中的商品"；

最后，报告应该是这样的：

[![Sample table][1]][1]

我尝试使用union/union all，但它没有带来想要的结果

以下是我迄今为止的单独查询：

1.
select part_no, INV_PART_API.Get_Description(contract,part_no), location_no, qty_onhand, qty_reserved,
(qty_onhand - qty_reserved) as "Available Qty"

from INV_PART_IN_STOCK
where location_no IN ('A','B','C', 'D') and qty_onhand > 0    -- Present


2. 
select part_no, 
INVENTORY_PART_API.Get_Description(contract,part_no),
location_no, 
qty_onhand as "On Hand (In Transit)", 
qty_reserved as "Reserved (In Transit)",
(qty_onhand - qty_reserved) as "Available Qty (In Transit)"

from INV_PART_IN_STOCK
where location_no IN ('GIT') and qty_onhand > 0                -- In Transit



3.
select part_no, description, 
buy_qty_due as "Qty in Production" from PURCHASE_ORDER_LINE
where objstate in ('Confirmed', 'Released') and demand_code_db = 'Invent Order' and
vendor_no = 11111         -- In Production

提前谢谢。

很抱歉，如果论坛上有类似的问题，我已经搜索了很长时间，但找不到接近的内容。

Answer 1

假设第一个部分总是返回该部分，则可以在其上保留联接。否则，可以通过完全外部联接来解决，或者(更好的方法(通过在中心部分表上驱动查询来解决，我希望您已经这样做了。

无论如何，使用CTE(with子句(，它可以这样写，但每个查询也可以内联，以便在子选择之间有一组联接。

with
VW_INSTOCK as
(
select 
part_no, INV_PART_API.Get_Description(contract,part_no) as part_description, location_no, qty_onhand, qty_reserved,
(qty_onhand - qty_reserved) as "Available Qty"
from INV_PART_IN_STOCK
where location_no IN ('A','B','C', 'D') and qty_onhand > 0)
),

VW_INTRANSIT as
(
select part_no, 
INVENTORY_PART_API.Get_Description(contract,part_no),
location_no, 
qty_onhand as "On Hand (In Transit)", 
qty_reserved as "Reserved (In Transit)",
(qty_onhand - qty_reserved) as "Available Qty (In Transit)"
from INV_PART_IN_STOCK
where location_no IN ('GIT') and qty_onhand > 0                -- In Transit
),

VW_INPROD as 
(
select 
part_no, description, 
buy_qty_due as "Qty in Production" 
from PURCHASE_ORDER_LINE
where 
objstate in ('Confirmed', 'Released') and demand_code_db = 'Invent Order' and
vendor_no = 11111         -- In Production
)
select
s.partno, 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)",
p."Qty in Production"
from
VW_INSTOCK s
left join VW_INTRANSIT t on t.part_no = s.part_no
left join VW_INPROD p on p.part_no = s.part_no