"select distinct GM_SKU.ITM_CD
from GM_SKU, GM_SKU2STORE, GM_INV_LOC
and GM_SKU.SKU_NUM = GM_SKU2STORE.SKU_NUM
and GM_SKU.SKU_NUM = GM_INV_LOC.SKU_NUM
group by GM_SKU.ITM_CD
having sum(GM_SKU2STORE.MIN_QTY) < 1
and sum(GM_INV_LOC.AVAIL_QTY) > 0"
I realize that this is ugly SQL but for some reason, this thing has got me lost. All I want to do is query for the "itm_cd" in the "gm_sku" table and return the item codes for everything that has a "sum(min_qty)<1" from the "gm_sku2store" table and an "avail_qty>0" from the "gm_inv_loc" table for the "store_cd='0001'". All 3 tables have multiple entries of the same SKU, but each SKU only has 1 "itm_cd". I'd like the results to show only one record for each item code but I keep getting:
SELECT DISTINCT evaluates the entire record as one entity. Therefore, if the SKU is different, it is viewed as a "distinct" record and will be printed. Try leaving off the DISTINCT. Since you are including GM_SKU.ITM_CD in the group by clause, you should only return one ITM_CD and the summation should execute properly.