Not a group by expression

"MM" <milica.mihac@ck.htnet.hrwrote, Wed 11 May 2005 12:29:19 +0200,
in comp.databases.oracle:

Quote: >Hello users. I have a query that I can run and see the result on the grid on >design time, but when i run the program i get the folowing >message:ORA-00979: not a GROUP BY expression. When I run the same query with >out parameters it runs and it was runing well before in Oracle 8.Also If I >don't use the GROUP BY function but with the parameter, it works also well >?What is the problem? The query is: > >select t1.id,t2.nad, max(t2.name) as name >from c02 t1, d00 t2 >where t1.id = t2.id >and t1.date1 >= :pDate1 >group by t2.nad, t2.id <-- is this supposed to be t1.id?

From Oracle's website: http://tinyurl.com/78fgq

ORA-00979 not a GROUP BY expression

Cause: The GROUP BY clause does not contain all the expressions in
the SELECT clause. SELECT expressions that are not included in a group
function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must
be listed in the GROUP BY clause.

Action: Include in the GROUP BY clause all SELECT expressions that
are not group function arguments.

HTH,
--
Teresa Robinson
Staff Programmer Analyst
Anteon Corporation
trobinson at anteon dot com

"MM" <milica.mihac@ck.htnet.hrwrote:

Quote: >Hello users. I have a query that I can run and see the result on the grid on >design time, but when i run the program i get the folowing >message:ORA-00979: not a GROUP BY expression. When I run the same query with >out parameters it runs and it was runing well before in Oracle 8.Also If I >don't use the GROUP BY function but with the parameter, it works also well >?What is the problem? The query is:select t1.id,t2.nad,max(t2.name) as >namefrom c02 t1,d00 t2where t1.id = t2.idand t1.date1>= :pDate1group by >t2.nad, t2.idAny help is helpfull. Regards,MM

Kako si? Give us the details of the DDL for the tables in question -
also, this question should really be asked on
comp.databases.oracle.server. Version number (exact) and OS info would
also help. Hvala.



Paul...



--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analyzing and attempting to help, please
do not top post.

Quote: Quote: >>Hello users. I have a query that I can run and see the result on the grid >>on >>design time, but when i run the program i get the folowing >>message:ORA-00979: not a GROUP BY expression. When I run the same query >>with >>out parameters it runs and it was runing well before in Oracle 8.Also If I >>don't use the GROUP BY function but with the parameter, it works also well >>?What is the problem? The query is:select t1.id,t2.nad,max(t2.name) as >>namefrom c02 t1,d00 t2where t1.id = t2.idand t1.date1>= :pDate1group by >>t2.nad, t2.idAny help is helpfull. Regards,MM > > Kako si? Give us the details of the DDL for the tables in question - also, this question should really be asked on comp.databases.oracle.server. Version number (exact) and OS info would also help. Hvala. > > > Paul... > > > --

Hi Paul. Thanks for question I'm fine.
I'm sure that problem is not in my SQL (as it work before in Oracle 8) as
error happens since I'm using Oracle 9.
Also it works if I change parametar with value. But when I change value with
parametar it doesn't work anymore.

The OS is WinXp all the time.

Table is:
-- Create table
create table C0NAPD1
(
BROJ_NALOGA NUMBER(8) not null,
RB NUMBER(4) not null,
IB_PROIZVODA VARCHAR2(10) not null,
KOLICINA NUMBER(12,2) not null,
DATUM_ISPORUKE DATE
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 22912K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table C0NAPD1
add constraint C02NAPD1_PK primary key (BROJ_NALOGA,RB)


Query is:
select c02.ib_proizvoda,

sum(c02.kolicina) as kolicina,

from c02napd1 c02

where

c02.datum_isporuke>= :pDatum1

group by c02.IB_PROIZVODA



Regards, Milica

Cause: The GROUP BY clause does not contain all the expressions in

Quote: the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause. Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.

Hi, I already included in GROUP BY clause all SELECT expressions that
are not group function arguments.
And it runs OK in Oracle 8, but when I change version of Oracle in Oracle
9...then error start to appear.

Regards, Milica

Quote: Quote: >>Hello users. I have a query that I can run and see the result on the grid >>on >>design time, but when i run the program i get the folowing >>message:ORA-00979: not a GROUP BY expression. When I run the same query >>with >>out parameters it runs and it was runing well before in Oracle 8.Also If I >>don't use the GROUP BY function but with the parameter, it works also well >>?What is the problem? The query is: >> >>select t1.id,t2.nad, max(t2.name) as name >>from c02 t1, d00 t2 >>where t1.id = t2.id >>and t1.date1 >= :pDate1 >>group by t2.nad, t2.id <-- is this supposed to be t1.id? > From Oracle's website: http://tinyurl.com/78fgq > ORA-00979 not a GROUP BY expression >

I find what was a problem..
I changed CursorLocation in TADOQuery from clUseClient u clUseServer and
now everything works OK.

Anyway thanks.
Regards, Milica

"Paul" <paulsnewsgroups@hotmail.comwrote in message
news:79e481d3bl3cs0nh4u325kbegjdqosiio6@4ax.com...

Quote: > "MM" <milica.mihac@ck.htnet.hrwrote: > Quote: >>Hello users. I have a query that I can run and see the result on the grid >>on >>design time, but when i run the program i get the folowing >>message:ORA-00979: not a GROUP BY expression. When I run the same query >>with >>out parameters it runs and it was runing well before in Oracle 8.Also If I >>don't use the GROUP BY function but with the parameter, it works also well >>?What is the problem? The query is:select t1.id,t2.nad,max(t2.name) as >>namefrom c02 t1,d00 t2where t1.id = t2.idand t1.date1>= :pDate1group by >>t2.nad, t2.idAny help is helpfull. Regards,MM > > Kako si? Give us the details of the DDL for the tables in question - also, this question should really be asked on comp.databases.oracle.server. Version number (exact) and OS info would also help. Hvala. > > > Paul... > > > -- > plinehan __at__ yahoo __dot__ __com__ > XP Pro, SP 2, > Oracle, 9.2.0.1.0 (Enterprise Ed.) Interbase 6.0.2.0; > When asking database related questions, please give other posters some clues, like operating system, version of db being used and DDL. The exact text and/or number of error messages is useful (!= "it didn't work!"). Thanks. > Furthermore, As a courtesy to those who spend time analyzing and attempting to help, please do not top post.

MM wrote:

Quote: Hello users. I have a query that I can run and see the result on the grid on design time, but when i run the program i get the folowing message:ORA-00979: not a GROUP BY expression. When I run the same query with out parameters it runs and it was runing well before in Oracle 8.Also If I don't use the GROUP BY function but with the parameter, it works also well ?What is the problem? The query is:select t1.id,t2.nad,max(t2.name) as namefrom c02 t1,d00 t2where t1.id = t2.idand t1.date1>= :pDate1group by t2.nad, t2.idAny help is helpfull. Regards,MM

Maybe you should be really careful about aggregate. You use

t1.id and t2.nad after SELECT as non-aggregate field

and

t2.id and t2.nad in GROUP BY

t2.id is not necessryy to be equal t1.id in DB processing statement.

And ofcourse I'm not sure that there is not another catch inside your
querry.

Regards
Gokky