group by and order by inside function using an sql-view

[niklas]

I have a problem with using both group by and order by inside an user defined function. When I try to compile the code below I get error: "You cannot define a TEMP-TABLE inside a function. (6411)". If I have the code inside a main procedure it works allright aswell as if I comment the "order by" or "group by" clause. Can anyone help? The example works with sports database and a SQL-view.

/***************
SQL view code start
***************/
DROP VIEW ALLORDERDATA.
CREATE VIEW ALLORDERDATA
(CUSTNUM,ORDERNUM,ORDERDATE,LINENUM,PRICE)
AS SELECT
ORDER.CUST-NUM,ORDER.ORDER-NUM,ORDER.ORDER-DATE,ORDER-LINE.LINE-NUM,
ORDER-LINE.PRICE
FROM ORDER, ORDER-LINE
WHERE ORDER.ORDER-NUM = ORDER-LINE.ORDER-NUM.
GRANT ALL PRIVILEGES ON ALLORDERDATA TO PUBLIC.
/***************
SQL view code end
***************/

/***************
code start
***************/
def temp-table tt_custname
field name like customer.name
field numcount as int.


function getdata returns integer (input cnum as integer, output table tt_custname):

def var m_name like customer.name.
def var m_numCount as int.
def var m_rowCount as int.

declare c1 cursor for
select
name, max(allorderdata.custnum)
from
customer, allorderdata, order
where
customer.cust-num = cnum and
customer.cust-num = custnum and
order.cust-num = customer.cust-num
group by
name
order by
name.

open c1.

repeat:
fetch c1 into m_name, m_numCount.
create tt_custname.
assign
tt_custname.name = m_name
m_rowCount = m_rowCount + 1.
end.

display m_rowCount.

end function.
/***************
code end
***************/