orecle select function 不是group by 表达式

[longdpt922]

select a.goodsid,
b.goodsname,
b.goodsspec,
b.unitname,
a.materialid,
c.goodsname as mgoodsname,
qty,
tl_recipecost(a.goodsid ,1) as cost1,
a.qty1,
tl_recipecost(a.goodsid ,2) as cost2,
a.qty2,
tl_recipecost(a.goodsid ,3) as cost3,
a.qty3,
tl_recipecost(a.goodsid ,4) as cost4
from recipe a, goods b, goods c
where a.goodsid = b.goodsid
and a.materialid = c.goodsid


create or replace function tl_recipecost
(
I_goodsid in int,
I_type in varchar2
)
return varchar2
--------------------------------------------

--------------------------------------------
as
v_caseno varchar2(64);
v_count int;
o_cost number(10,6);
begin


o_cost:=0;


if(I_type =1 )then

For R in
(
select min(a.cost) AS COST, b.materialid ,b.qty ,b.goodsid
from goodsshop a ,recipe b where a.goodsid = b.materialid and b.goodsid = i_goodsid
group by b.materialid ,b.qty ,b.goodsid
)
loop
o_cost := o_cost+ R.COST*b.qty;

end loop ;


elsif (I_type =2 )then
For R in
(
select min(a.cost) AS COST, b.materialid ,b.qty1 ,b.goodsid
from goodsshop a ,recipe b where a.goodsid = b.materialid and b.goodsid = i_goodsid
group by b.materialid ,b.qty ,b.goodsid
)
loop
o_cost := o_cost+ R.COST*b.qty1;

end loop ;
elsif (I_type =3 )then
For R in
(
select min(a.cost) AS COST, b.materialid ,b.qty2 ,b.goodsid
from goodsshop a ,recipe b where a.goodsid = b.materialid and b.goodsid = i_goodsid
group by b.materialid ,b.qty ,b.goodsid
)
loop
o_cost := o_cost+ R.COST*b.qty2;

end loop ;
elsif (I_type =4 )then
For R in
(
select min(a.cost) AS COST, b.materialid ,b.qty3 ,b.goodsid
from goodsshop a ,recipe b where a.goodsid = b.materialid and b.goodsid = i_goodsid
group by b.materialid ,b.qty ,b.goodsid
)
loop
o_cost := o_cost+ R.COST*b.qty3;

end loop ;
end if;



v_caseno:=to_char(o_cost);
return o_cost;
end ;

执行提示不是group by表达式？？为啥？该怎么改？

[一颗向往数据的心丶]

你判断I_type 是否等于2，3，4里面 select min(a.cost) AS COST, b.materialid ,b.qty1 ,b.goodsid from goodsshop a ,recipe b where a.goodsid = b.materialid and b.goodsid = i_goodsid group by b.materialid ,b.qty ,b.goodsid I_type =3和I_type =4里面类似错误

[CT_LXL]

用了聚合函数需用GROUP BY,#1 +1

[_拙计]

你group by的和你select的 字段不统一 like #1