Hi,

In Fact your query wont run if you put the group by in a function.
SELECT trandate, trantype, sum(tranamt) AS tranamt FROM mytran ;
WHERE bookyear = ?cAcctyear AND accode = ?nAccode ;
GROUP BY Month(trandate), trantype

Would give:
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
On the trandate column.


So...
1)

But you could make a function Like:

SQL> CREATE OR REPLACE FUNCTION Month
2 (p_date DATE) RETURN CHAR IS
3 BEGIN
4 RETURN to_char(p_date,'MM');
5 END Month;
6 /

Function created.

SQL> select Month(sysdate) from dual;

MONTH(SYSDATE)
--------------------------------------------------------------------------------
07

Not sure about performance impacts being a function call in a GROUP BY.
But try it out..

2)
The next point is that when you call a function in a group by will get the error
ORA-00979: not a GROUP BY expression
On the trandate column.

Maybe you can live with
SELECT trantype, sum(tranamt) AS tranamt FROM mytran ;
WHERE bookyear = ?cAcctyear AND accode = ?nAccode ;
GROUP BY Month(trandate), trantype

OR

SELECT Month(trandate), sum(tranamt) AS tranamt FROM mytran ;
WHERE bookyear = ?cAcctyear AND accode = ?nAccode ;
GROUP BY Month(trandate), trantype

Not sure about this point maybe someone else knows...
***************************************************************************************************************************************************
I have a SPT query.

SELECT trandate, trantype, sum(tranamt) AS tranamt FROM mytran ;
WHERE bookyear = ?cAcctyear AND accode = ?nAccode ;
GROUP BY Month(trandate), trantype


I want to make this query to be executed on any remote database including VFP. The above executes in VFP without any
problem. But in Oracle is not and the reason is I do not know the equivalent function for MONTH() that I have used under my "group
by" clause. I guess TO_CHAR(trandate, "MM") should work with Oracle but I do not want to go by that as it will not work with other
databases. Please help.

Thanks in advance.