compiles and executes fine on 8.1.7.
Don't know about 9i.
John Darrah wrote:
>
> Hello,
>
> I have a development instance running 9.0.1 on Solaris 2.8 and I am
> encountering an error when I run the following plsql procedure. The
> support analyst at Oracle said this ran without error when he tried it
> on his system so I'm guessing its environmental but I would like see if
> other installations experience this as well. If anyone is running 9i
> and feels comfortable doing so, could you run this procedure and report
> your results.
>
> create or replace procedure test_grp(i_grouping IN VARCHAR2,
> i_fmt IN VARCHAR,
> i_start_dt IN VARCHAR2,
> i_end_dt IN VARCHAR2)
> AS
> TYPE o_cur IS REF CURSOR;
> cc o_cur;
> m_trunc VARCHAR2(100);
> m_dt_format VARCHAR2(100);
> m_start_dt DATE := '01-JAN-2000';
> m_end_dt DATE := sysdate;
> BEGIN
> m_trunc := i_grouping;
> m_dt_format := i_fmt;
> OPEN cc FOR
> SELECT
> t1.period,
> SUM(t1.total_rec) "total"
> FROM(
> SELECT
> TO_CHAR(created,m_dt_format) period,
> object_type,
> COUNT(*) total_rec
> FROM user_objects
> WHERE (created >= m_start_dt AND created < m_end_dt)
> GROUP BY TO_CHAR(created,m_dt_format),
> object_type
> UNION ALL
> SELECT
> TO_CHAR(t1.created,m_dt_format) period,
> t1.object_type,
> COUNT(*) total_rec
> FROM user_objects t1
> WHERE (t1.created >= m_start_dt AND t1.created < m_end_dt)
> GROUP by TO_CHAR(t1.created,m_dt_format),
> t1.object_type
> ) t1
> GROUP BY t1.period;
> END;
> /
> sho err
> exec test_grp('DD','YYYY/MM/DD',null,null);
>
> Here is the error I get:
> ERROR at line 1:
> ORA-00979: not a GROUP BY expression
> ORA-06512: at "TEST.TEST_GRP", line 15
> ORA-06512: at line 1
>
> Thanks,
>
> John
>
> --
> Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Received on Mon Feb 18 2002 - 21:36:20 CST
