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

    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:

    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
    Fort Worth, Texas, USA
    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