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