I have been using a SQL Query to build this report for the last couple of weeks and have hit a snag. I have 4 main tables that look like:


Table - GM_INV_LOC

SKU_NUM AVAIL_QTY LOC_CD
------------ --------- -------
000018716-04 9 0023
000018716-04 12 0124

Table - GM_SKU

SKU_NUM ITM_CD
------------ ------------
000019986-00 000019986
000019986-01 000019986


Table - GM_SKU2STORE

SKU_NUM MIN_QTY MAX_QTY
------------ ------- -------
000842172-04 1 1
000842172-04 1 1


Table - GM_ITM
ITM_CD DES1 VSN DIV_CD
----------- ------- --- --------
000842172 HAT 101 1


All I want to do is query for the "sku_num" in the "gm_sku" table and return the item codes for everything that has a "sum(min_qty)>0" from the "gm_sku2store" table and an "avail_qty>0" from the "gm_inv_loc" table (for loc_cd='0001') or DOES NOT EXIST AT ALL in the "gm_sku2store" table but still has an "avail_qty>0" from the "gm_inv_loc" table (for loc_cd='0001'). All 4 tables have multiple entries of the same SKU, but each SKU only has 1 "itm_cd".

I'm using the following statement:

select GM_SKU.SKU_NUM, GM_ITM.DES1, GM_ITM.VSN, GM_ITM.VE_CD, GM_ITM.UDF1, GM_ITM.DIV_CD
from GM_SKU, GM_SKU2STORE, GM_INV_LOC, GM_ITM
where GM_INV_LOC.STORE_CD='0001'
and GM_SKU.SKU_NUM = GM_SKU2STORE.SKU_NUM
and GM_SKU.SKU_NUM = GM_INV_LOC.SKU_NUM
and GM_SKU.ITM_CD = GM_ITM.ITM_CD
group by GM_SKU.SKU_NUM
having sum(GM_SKU2STORE.MIN_QTY) < 1
and sum(GM_INV_LOC.AVAIL_QTY) > 0