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?

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