Toad World® Forums

Invalid SQL statement parse return


#1

Dear Sir,
when I execute a SQL statement in SQL Editor as following:
select sc_id, count(*)
from wrk_sc_detail
group by sc_id;

It returned an error message:
ORA-00937: not a single-group group function

regares,
att1.dat (3.91 MB)


#2

Hello Kent,

I cannot reproduce the problem. Could you please send the DDL definition for your table. Also, can you confirm that the SQL works correctly in a previous version of SQL Navigator or any other Oracle tools?

Thank you,
Roman


#3

Hi Roman,

The SQL can be executed in previous version. the table definition as follow:

create table wrk_sc_detail
(sc_id number(8,0),
seq_no number(3,0),
worknum number(10,0),
worksoc number(3,0),
dur_min number(4,0),
dur_sec number(2,0)
,
constraint pk_wrk_sc_detail
primary key (sc_id, seq_no)
using index
tablespace indx)

Regards,
codeeditor.jpeg


#4

Kentlai, Roman,

I can reproduce this problem - as can be seen in the attached screen shot.

I also have a woraround though: put some dummy text between the table name and the “group by” statement.

For the statement I tried, e.g.:
select count(1), tekstkod from gen_tekst where 1 = 1 group by tekstkod;

It would seem the “group by” stuff gets lopped off before handing it over to the DB, triggering the error.

Edit: added workaround.
SqlNav6_group_by.png


#5

Hi,
I tested it on 10g database with different optimizer_features_enable and some states of index, no results, query works.
So I think this is somee kind of error of Oracle client, combined with optimizer_mode(goal) parameter.

Can You paste results of those queries ?
select name,value from v$parameter where name in (‘optimizer_mode’,‘compatible’,‘optimizer_features_enable’);

select * from nls_session_parameters;

Regards Piter


#6

As you requested (please view with a fixed-width font):

select name,value from v$parameter where name in (‘optimizer_mode’,‘compatible’,‘optimizer_features_enable’);

NAME VALUE


compatible 9.2.0.7.0
optimizer_features_enable 9.2.0
optimizer_mode CHOOSE

select * from nls_session_parameters;

PARAMETER VALUE


NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-Mon-RRRR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

I might add here that a statement like:

select *
from gen_tekst
order by tekstkod desc;

will also NOT perform the “order by”.
Moreover, if I type in

select *
from gen_tekst
order by tekstkod desc
kjshgkjhgakdh
;

I still get the - unordered - list (no mention of the syntax mistake).

Edit: added “order by” failure.


#7

Hi everyone,

I managed to reproduce these bugs in build 1014. It causes by a fix we made to allow more tolerance when executing SQL statements. The good news is all of the above have been fixed in our internal build.

Gwen


#8

Gwen,

That was one quick fix! Thanks for following up on this problem.

As this is a serious problem (for me and “Kentlai”, at least), when can we expect this fix to be available for us mere mortals?


#9

we’re still fixing a couple of other issues so hopefully we can get another build out for you guys in a couple of days. We have to make sure the build is stable.

Thanks a lot for your feedbacks.
Gwen


#10

Confirmed fixed in Build 1028.


#11

checked. this bug was fixed .


#12

Thanks for the updates guys !!

  • Jaime -