创建与JOINS有问题的视图 [英] Create View having trouble with JOINS

问题描述

我有5个地区，每个地区包含1个商店和1个仓库。我有一个表，其中包含区域和相应的商店和仓库。我也有一个表，列出了商店和仓库的库存。最后，有一个表格列出仓库的允许数量。如果我现在查看库存表，现在它会说如下：

 设施ID ITEM_NUMBER QTY 
 STORE 1 15D 2 
 WAREHOUSE 1 15D 1 
  

问题是，商店1和仓库1属于地区1，我想以下列格式显示：

  REGION仓库数量OH商店数量OH 
 1 1 2 1 1 
  

等等所以我写了以下SQL：

创建或替换视图REGION_BALANCES
作为
与WAREHOUSES作为
（
选择

A.REGION_CODE，A.REG_DESC，A.WAREHOUSE_NUMBER，A.FACILITY_ID，C.ITEM_NUMBER，sum（C.IN_STOCK_QTY）为IN_STOCK_QTY，B.ALLOWED_QTY
从REG_WHS_STR_ASSOC A
加入ALLOWANCES B on （A.FACILITY_ID = B.FACILITY_ID）
通过A.REGION_CODE，A.REG_DESC，A.WAREHOUSE_NUMBER加入INVENTORIES C（A.FACILITY_ID = FACILITY_ID）和B.ITEM_NUMBER = C.ITEM_NUMBER）
，C.ITEM_NUMBER，B.ALLOWED_QTY
），
存储为
（
选择

A.REGION_CODE，A.REG_DESC，A.STORE_NUMBER，来自REG_WHS_STR_ASSOC的$ F
加入INVENTORIES B（A.FACILITY_ID = B.FACILITY_ID）
组由A.REGION_CODE加入INVENTORIES B，并将其作为IN_STOCK_QTY
加入A.FACILITY_ID，B.ITEM_NUMBER， A.REG_DESC，A.STORE_NUMBER，B.ITEM_NUMBER
）
选择A.REGION_CODE，A.REG_DESC，A.WAREHOUSE_NUMBER，A.FACILITY_ID，WAREHOUSES.ITEM_NUMBER，WAREHOUSES.IN_STOCK_QTY，WAREHOUSES.ALLOWED_QTY，STORES .STOREESNUMBER，STORES.FACILITY_ID，STORES.ITEM_NUMBER，STORES.ITEM_NUMBER，STORES.IN_STOCK_QTY
来自REG_WHS_STR_ASSOC A
加入WAREHOUSES（A.REGION_CODE = WAREHOUSES.REGION_CODE）
加入STORES（A.REGION_CODE = STORES .REGION_CODE）
order by 5,1 asc;


我已将我的连接从左连接改为右连接，交叉连接到内连接;然而，我得到30条记录（我需要数百条记录），或者我得到笛卡尔产品。

提前感谢！

解决方案

您忘了按料品编号连接仓库和商店（加左连接是更好的情况）

 创建或替换视图REGION_BALANCES 
作为
与WAREHOUSES为
（
选择
 A.REGION_CODE，A.REG_DESC，A.WAREHOUSE_NUMBER，
 .FACILITY_ID，C.ITEM_NUMBER，sum（C.IN_STOCK_QTY）为IN_STOCK_QTY，B.ALLOWED_QTY 
从REG_WHS_STR_ASSOC A 
加入ALLOWANCES B（A.FACILITY_ID = B.FACILITY_ID）
加入INVENTORIES C on（A.FACILITY_ID = FACILITY_ID）and B.ITEM_NUMBER = C.ITEM_NUMBER）
 group by A.REGION_CODE，A.REG_DESC，A.WAREHOUSE_NUMBER，C.ITEM_NUMBER，B.ALLOWED_QTY 
），
 STORES as 
（
 select 
 
 A.REGION_CODE，A.REG_DESC，A.STORE_NUMBER，
 A.FACILITY_ID，B.ITEM_NUMBER，sum（B。 IN_STOCK_QTY）as IN_STOCK_QTY 
 from REG_WHS_STR_ASSOC A 
通过A.REGION_CODE，A.REG_DESC，A.STORE_NUMBER，B.ITEM_NUMBER $在（A.FACILITY_ID = B.FACILITY_ID）
上加入INVENTORIES b $ b）
选择A.REGION_CODE，A.REG_DESC，A。 WAREHOUSE_NUMBER，A.FACILITY_ID，
 WAREHOUSES.ITEM_NUMBER，WAREHOUSES.IN_STOCK_QTY，WAREHOUSES.ALLOWED_QTY，
 STORES.STORE_NUMBER，STORES.FACILITY_ID，STORES.ITEM_NUMBER，STORES.IN_STOCK_QTY 
，来自REG_WHS_STR_ASSOC A 
 left join WAREHOUSES on（A.REGION_CODE = WAREHOUSES.REGION_CODE）
 left join STORES on 
（A.REGION_CODE = STORES.REGION_CODE AND STORES.ITEM_NUMBER = WAREHOUSES.ITEM_NUMBER）
 order by 5,1 asc; 
  

I have 5 regions that each contain 1 store and 1 warehouse. I have a table that contains the regions and corresponding stores and warehouses. I also have a table that lists the inventories of both the stores and warehouses. Finally, have a table that lists the allowed quantities for the warehouse. If I look at the inventory table right now it would say something like:

FACILITY ID        ITEM_NUMBER    QTY
  STORE 1              15D         2 
 WAREHOUSE 1           15D         1

The problem is, Store 1 and Warehouse 1 belong to region 1 and I would like to see it in this format:

REGION    WAREHOUSE    QTY OH     STORE    QTY OH 
  1           1          2          1         1

and so on... So I wrote the following SQL:

Create or replace view REGION_BALANCES
as
with WAREHOUSES as 
(
select

A.REGION_CODE,A.REG_DESC,A.WAREHOUSE_NUMBER,A.FACILITY_ID,C.ITEM_NUMBER,sum(C.IN_STOCK_QTY) as IN_STOCK_QTY,  B.ALLOWED_QTY
from REG_WHS_STR_ASSOC A
join ALLOWANCES B on (A.FACILITY_ID = B.FACILITY_ID)
join INVENTORIES C on (A.FACILITY_ID = FACILITY_ID) and B.ITEM_NUMBER = C.ITEM_NUMBER)
group by A.REGION_CODE,A.REG_DESC,A.WAREHOUSE_NUMBER,C.ITEM_NUMBER,B.ALLOWED_QTY
), 
STORES as 
(
select

 A.REGION_CODE,A.REG_DESC,A.STORE_NUMBER,A.FACILITY_ID,B.ITEM_NUMBER,sum(B.IN_STOCK_QTY) as IN_STOCK_QTY
from REG_WHS_STR_ASSOC A
join INVENTORIES B on (A.FACILITY_ID = B.FACILITY_ID)
group by A.REGION_CODE,A.REG_DESC,A.STORE_NUMBER, B.ITEM_NUMBER
)
select A.REGION_CODE,A.REG_DESC,A.WAREHOUSE_NUMBER,A.FACILITY_ID,WAREHOUSES.ITEM_NUMBER,WAREHOUSES.IN_STOCK_QTY,WAREHOUSES.ALLOWED_QTY,STORES.STORE_NUMBER,STORES.FACILITY_ID,STORES.ITEM_NUMBER,STORES.IN_STOCK_QTY
from REG_WHS_STR_ASSOC A
join WAREHOUSES on (A.REGION_CODE = WAREHOUSES.REGION_CODE)
join STORES on (A.REGION_CODE = STORES.REGION_CODE)
order by 5,1 asc;

I have changed my join around from left joins to right joins to cross joins to inner joins; however, I either get 30 records (and I need hundreds of records) or I get Cartesian products. Can anyone provide advice to what I'm doing wrong?

Thanks in advance!

解决方案

You were forgetting to link warehouses and stores by item number (plus left join is the better for this case)

Create or replace view REGION_BALANCES
as
with WAREHOUSES as 
(
select
A.REGION_CODE, A.REG_DESC, A.WAREHOUSE_NUMBER, 
A.FACILITY_ID, C.ITEM_NUMBER,sum(C.IN_STOCK_QTY) as IN_STOCK_QTY,  B.ALLOWED_QTY
from REG_WHS_STR_ASSOC A
join ALLOWANCES B on (A.FACILITY_ID = B.FACILITY_ID)
join INVENTORIES C on (A.FACILITY_ID = FACILITY_ID) and B.ITEM_NUMBER = C.ITEM_NUMBER)
group by A.REGION_CODE,A.REG_DESC,A.WAREHOUSE_NUMBER,C.ITEM_NUMBER,B.ALLOWED_QTY
), 
STORES as 
(
select

A.REGION_CODE,A.REG_DESC,A.STORE_NUMBER, 
A.FACILITY_ID,B.ITEM_NUMBER,sum(B.IN_STOCK_QTY) as IN_STOCK_QTY
from REG_WHS_STR_ASSOC A
join INVENTORIES B on (A.FACILITY_ID = B.FACILITY_ID)
group by A.REGION_CODE,A.REG_DESC,A.STORE_NUMBER, B.ITEM_NUMBER
)
select A.REGION_CODE, A.REG_DESC,A. WAREHOUSE_NUMBER, A.FACILITY_ID, 
WAREHOUSES.ITEM_NUMBER, WAREHOUSES.IN_STOCK_QTY, WAREHOUSES.ALLOWED_QTY,
STORES.STORE_NUMBER, STORES.FACILITY_ID, STORES.ITEM_NUMBER, STORES.IN_STOCK_QTY
from REG_WHS_STR_ASSOC A
left join WAREHOUSES on (A.REGION_CODE = WAREHOUSES.REGION_CODE)
left join STORES on 
 (A.REGION_CODE = STORES.REGION_CODE AND STORES.ITEM_NUMBER = WAREHOUSES.ITEM_NUMBER)
order by 5,1 asc;

这篇关于创建与JOINS有问题的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！