Re: parameter field not working in group by clause

Gerry wrote:

> Hello,
> I have a problem with a parameter field in my statement of
> queryDataset.
> Because my select uses SUM(), i have to add a group by clause with the
> parameter field, but this always produces an ora-error:
>
> ORA-00979 not a GROUP BY expression
>
> this is the statement i use:
>
> SELECT RPAD( SUBSTR( TO_CHAR( TITEL ), 1, :STELLEN ), 5, '0' ) AS
> TITEL,
> SUM( BETRAG )
> FROM KONTO
> WHERE TO_CHAR( TITEL ) LIKE ( SUBSTR( TO_CHAR( :TITEL ), 1, :STELLEN )
> || '%' )
> GROUP BY RPAD( SUBSTR( TO_CHAR( TITEL ), 1, :STELLEN ), 5, '0' )
>
> Using a preparedStatement returns the same error. When i set a number
> instead of the parameter it works fine.
>
> We use an oracle 8.1.7 database and I tested the statement using
> severel jdbc-thin drivers ( 8.1.6, 8.1.7.1, 9.2.0.1 ) without success.
> Do anyone know something about that error?
>
> any solution would be helpful.
>
> Thanks
> Gerry

I suspect the problem is your use of bind variables because Oracle has no idea what will be in them at run time. And if this is being run in a procedure there is no reason not to use typed variable definitions.

Daniel Morgan Received on Thu Jul 11 2002 - 10:00:19 CDT