ORA-00937: not a single-group group function

ORA-00937: not a single-group group function

Post by Bryan Vale » Thu, 13 Jan 2005 04:51:02


IF you're doing a select max() using a TADOQuery, look out for this.

http://www.yqcomputer.com/
 
 
 

ORA-00937: not a single-group group function

Post by Matt Jacob » Thu, 13 Jan 2005 06:23:29


I'd have set that query to read-only to begin with. :-)

However, the fact it added rowid to a query calling an aggregate
function is kind of lame. But how would it know without parsing the
query and having intelligence in regard to aggregate function names?

 
 
 

ORA-00937: not a single-group group function

Post by Bryan Vale » Thu, 13 Jan 2005 10:49:10

How about this: The SQL component should pass in the SQL that I wrote
without modification. If it needs RowID to do it's job, it should give me a
"you forgot RowID" error.

I really hate 'smart' software that tries to outthink me as a programmer.
If I forget something, there should be an exception - the component should
never try to second-guess what I am doing or why.
 
 
 

ORA-00937: not a single-group group function

Post by Bryan Vale » Fri, 14 Jan 2005 04:44:30

Both.

But the problem is solved. I just set the ADOQuery's lock type to
ltReadOnly, and now all is well.
 
 
 

ORA-00937: not a single-group group function

Post by Matt Jacob » Fri, 14 Jan 2005 06:22:23


It cannot do that if you specify optimistic locking.

By specifying optimistic locking, the database component has to assume
you want a dataset that can be modified via Edit(). And in that case,
it needs a RowID or PK to perform the update.


It's a double-edged sword, I'll grant you that.
 
 
 

ORA-00937: not a single-group group function

Post by Bryan Vale » Fri, 14 Jan 2005 06:51:45


It's odd that this is the default setting for the TADOQuery, then. So every
time I plunk a TADOQuery onto a Datamodule, I have to remember to change it
to ltReadOnly? That's dumb.

I rarely to never use a Query to edit() data. I use them to select, insert,
update, or delete. I really detest that it modifies the SQL. That makes it
a million times harder to debug.

When you write the SQL:

Select count(CITIES) from STATES where STATE='CA'

This is a single-group function. It works in PL/SQL developer. Then,
because you didn't explicitly stop it from doing so, when you put it in a
Delphi App hoping to learn how many cities there are in California, you get:

ORA-00937: not a single-group group function

Maybe I'm too much of a purist, but my view is that this hasn't made my life
any easier.