jeudi 8 janvier 2015

Help with Joins - Cartesian Product


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!





Aucun commentaire:

Enregistrer un commentaire