SQL Query Question

[heprox]

I've been working with a SQL query for the last day or so that I can't seem to get working. I'm running an Oracle 8i instance for this query on AIX 4.3.3. I have 3 tables with the following structure:

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

I'm using the following query:

"select GM_SKU.ITM_CD, GM_SKU.SKU_NUM from GM_SKU
where GM_SKU.SKU_NUM=GM_SKU2STORE.SKU_NUM in
(select GM_SKU2STORE.SKU_NUM from GM_SKU2STORE where sum(MIN_QTY)>0) and GM_SKU.SKU_NUM=GM_INV_LOC.SKU_NUM in
(select GM_INV_LOC.SKU_NUM from GM_INV_LOC where GM_INV_LOC.AVAIL_QTY>1) order by GM_SKU.ITM_CD"

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)>0" from the "gm_sku2store" table and an "avail_qty>0" from the "gm_inv_loc" table. All 3 tables have multiple entries of the same SKU, but each SKU only has 1 "itm_cd". Anyone know what I'm doing wrong here? Probably everything...

 

[JarlH]

Try:

select ITM_CD from GM_SKU
where SKU_NUM in
(select SKU_NUM from GM_SKU2STORE
group by SKU_NUM
having sum(MIN_QTY) > 0)
and SKU_NUM in
(select SKU_NUM from GM_INV_LOC
where AVAIL_QTY > 0)
order by ITM_CD;

Perhaps it works...

/Jarl

 

[heprox]

I took the statement and added another condition:

select distinct GM_SKU.ITM_CD, GM_SKU.SKU_NUM
from GM_SKU, GM_SKU2STORE, GM_INV_LOC
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
group by GM_SKU.ITM_CD, GM_SKU.SKU_NUM
having sum(GM_SKU2STORE.MIN_QTY) < 1
and sum(GM_INV_LOC.AVAIL_QTY) > 0

...to have the query only look for records that are in a &quot;STORE_CD='0001'&quot;, this should cut down the query time. However I's like to get the query to only show one record per item code instead of:

ITM_CD SKU_NUM
------------ ------------
000015124 000015124-24
000023952 000023952-02
000023952 000023952-03
000023952 000023952-04
000023952 000023952-05
000023952 000023952-06
000839804 000839804-03
000839804 000839804-04
000859517 000859517-01
000859517 000859517-02
000859517 000859517-03
000859517 000859517-04
923346832 923346832-11

...all I really need is just the item code. Maybe removing the SKU_NUM from the select statement?

 

[rrrkrishnan]

This should work.

select distinct GM_SKU.ITM_CD
from GM_SKU, GM_SKU2STORE, GM_INV_LOC
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
group by GM_SKU.ITM_CD
having sum(GM_SKU2STORE.MIN_QTY) < 1
and sum(GM_INV_LOC.AVAIL_QTY) > 0