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

    Question Unanswered: SQL Function Query

    I have 4 tables in an Oracle 8i database they are:

    Table GM_PRC

    sku_num itm_cd prc_zone_cd prc_grp_cd beg_dt ret_prc
    --------- -------- ----- ----- -------- ------
    000000848-01 000000848 GEN GEN 1-JAN-2004 7.99
    000000848-02 000000848 GEN GEN 1-JAN-2004 7.99
    000000848-03 000000848 GEN GEN 1-JAN-2004 7.99
    000000848-04 000000848 GEN GEN 1-JAN-2004 7.99

    Table GM_ITM

    itm_cd des
    --------- -----------
    000000848 truck


    Table GM_SKU

    sku_num itm_cd color_des
    --------- ---------- ---------
    000000848-01 000000848 Black
    000000848-02 000000848 Black
    000000848-03 000000848 Black
    000000848-04 000000848 Black


    Table GM_INV_LOC

    sku_num avail_qty
    --------- --------
    000000848-01 2
    000000848-02 4
    000000848-03 6
    000000848-04 10

    I'm trying tp use a function in the database that returns the most recent price (based on beg_dt) for a given field ("sku_num"). I need to use this function by querying an "itm_cd" and having it return all of the "sku_num" that have that itm_cd. I'm don't care where I extract "itm"cd from but I will have to get the "des" from the GM_ITM table. The problem is my query only returns partial results:

    select gm_itm.itm_cd,gm_sku.sku_num,color_des,gm_baseprc. ret_prc, sum(avail_qty)
    from gm_inv_loc,gm_sku,gm_itm,
    (select cs_prc_util.eff_perm_prc(sku_num,prc_zone_cd,prc_g rp_cd) as ret_prc
    from gm_prc
    where sku_num ='000000848-01'
    and prc_zone_cd='GEN'
    and prc_grp_cd='GEN') baseprc
    where gm_itm.itm_cd = '000000848'
    and gm_itm.itm_cd=gm_sku.itm_cd
    and gm_sku.sku_num=gm_inv_loc.sku_num
    group by gm_itm.itm_cd,gm_sku.sku_num,color_des,baseprc.ret _prc


    ...this only returns:

    ITM_CD SKU_NUM COLOR_DES RET_PRC SUM(AVAIL_QTY)
    000000848 000000848-02 Black 7.99 23
    000000848 000000848-03 Black 7.99 12


    ...the "avail_qty" and "color_des" are correct, however there I'm only getting partial results?

  2. #2
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    I'm assuming the actual data does not match your sample data, since there's no way to sum any combination of 2, 4, 6 and 10 and get 23.

    Your baseprc query will force all RET_PRC to the value of a single sku_num, though the table structure implies they could vary.

    The GM_INV_LOC table is a little suspect. For example, if when avail_qty is zero, does a record appear?

    In my experience (long-running queries), user-defined functions are to be tried just before wrist-slashing. Can you use an analytic function?

    Try something like this (not tested):

    select gm_itm.itm_cd,gm_sku.sku_num,color_des,
    LAST_VALUE (gm_prc.ret_prc) OVER (PARTITION BY gm_prc.sku_num ORDER BY gm_prc.beg_dt) last_prc, sum(avail_qty) tot_avail
    from gm_inv_loc, gm_sku, gm_itm
    where gm_itm.itm_cd = '000000848'
    and gm_itm.itm_cd=gm_sku.itm_cd
    and gm_sku.sku_num=gm_inv_loc.sku_num(+) --outer join on gm_inv_loc
    group by gm_itm.itm_cd,gm_sku.sku_num,color_des

    Index on gm_prc.sku_num, gm_prc.beg_dt helpful

Posting Permissions

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