Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    54

    Question Unanswered: SQL Query Question

    I've been working with a SQL query for the last day or so that I can't seem to get it working correctly. 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 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"

    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:

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


    ...perhaps removing the "SKU_NUM" reference from the select statement?

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    i am a bit confused here. The select u show, has only one column in the select clause, but the results have to columns ??????
    If you only want the ITM_CD to be shown and have it distinct (so only one value is shown) yes, you'll have to drop the second column (SKU_NUM). Since each ITM_CD can have multiple SKU_NUM's, showing them both will give u the result set shown. Select DISTINCT means that oracle filter out all rows from the result set that are really the same. Since u display the SKU_NUM column also and ,ultiple SKU_NUMs are referring to the same ITM_CD, multiple rows of are shown per ITM_CD.

    Just take a look at your resultset and see that all the rows are different because of the SKU_NUM.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •