Solved

# ora-00937 not single-group group function

Posted on 2004-09-20
865 Views
I've already posted this question to which I got an answer from Henka,
but it doesn't work, perhaps because I have oversimplified the problem.

This is the formulation of the function which more closely resembles the
actual problem for which "GROUP BY" doesn't cut it because with it I get
another error - ora-00979: not a GROUP BY expression:

(iA_ID IN INTEGER)
RETURN  VARCHAR2        IS
sAName    VARCHAR2(10);
BEGIN   sAName    := null;
SELECT  decode(count(*),0,NULL, decode(ptest,0,NULL,'P') || '-' ||
decode(stest,0,NULL,"s"))
INTO    sAName
FROM    A
WHERE   A_ID = iA_ID
GROUP BY ptest;
RETURN  sAName;
END;

The actual query contains two dozens concatenated DECODEs.
Placing any one of them in the GROUP BY clause causes the said
error ora-00979. Placing all of them in that clause would be ludicrous.
And besides, I fear the GROUP BY clause would cause the same error
even in the simple context I posted first.

Have you tested your suggestion, Henka, or was it a general hint?
0
Question by:dkrnic
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 77

Expert Comment

ID: 12105482
Without more information this will be almost impossible to answer.  Personally, I would need a table description and some test data with some expected results.

That said:
The following works for me:
declare
sAName varchar2(10);
begin
SELECT  decode(count(*),0,NULL, decode(0,0,NULL,'P') || '-' || decode(0,0,NULL,'s'))
INTO      sAName
FROM    dual
WHERE   dummy = 'X'
GROUP BY dummy;

end;
/

As a possible work-around try something like (it's ugly but should work):

RETURN  VARCHAR2        IS
myCount   NUMBER;
BEGIN   sAName    := null;
SELECT count(*) into myCount  FROM    A  WHERE   A_ID = iA_ID;

SELECT decode(myCount,0,NULL, decode(ptest,0,NULL,'P') || '-' ||
decode(stest,0,NULL,"s"))
FROM.....
END;
0

LVL 1

Expert Comment

ID: 12154059
SELECT  decode(count(*),0,NULL, decode(ptest,0,NULL,'P') || '-' ||
decode(stest,0,NULL,"s"))
INTO    sAName
FROM    A
WHERE   A_ID = iA_ID
GROUP BY ptest;

There are two errors.
1. If you are using in select clause stest column you have to use then in group by clause too.
2. "s" means column with name "s" - small s.
I think that you must change your query into:
SELECT  decode(count(*),0,NULL, decode(ptest,0,NULL,'P') || '-' ||
decode(stest,0,NULL,'s'))
INTO    sAName
FROM    A
WHERE   A_ID = iA_ID
GROUP BY ptest, stest;

Good luck
0

LVL 1

Accepted Solution

Kagiejot earned 250 total points
ID: 12154069
And of course your query must return one and only one record because this is SELECT INTO statement.
If not, there will be TOO_MANY_ROWS exception or NO_DATA_FOUND.

Good luck
0

Author Comment

ID: 12155453
Yes you are right Kagiejot, but the query does return at most 1 value anyways.
And you're also right about having to repeat all components of
the query in the GROUP BY clause, which means that Henka was also
right. Since the query never yields more than 1 row, if at all, this leads to
an absurd construct:

FUNCTION getOrderLongName(iA_ID IN INTEGER)
RETURN  VARCHAR2        IS
sAName    VARCHAR2(100);
BEGIN   sAName    := NULL;
SELECT  decode ( count(*), 0, NULL, pza.code  ||  prz.planlevel  || '-' || prz.planyear ||
DECODE ( prz.project, NULL, '', '_' || RTRIM ( prz.project ) ) ||
' V' || LPAD ( TO_CHAR ( pfv.version ), 4, '0' ) ||
' A' || TO_CHAR ( iA_ID ) || ' (' ||
DECODE ( auf.ptest,     0, NULL, 'P' ) ||
DECODE ( auf.diffdoc,      0, NULL, 'D' ) ||
DECODE ( auf.offernr,         0, NULL, 'A' ) ||
DECODE ( auf.demandmatrix,    0, NULL, 'N' ) ||
DECODE ( auf.assign,           0, NULL, 'U' ) ||
DECODE ( auf.stopoverresistance, 1, 's', NULL ) ||
DECODE ( auf.vehicledim,             0, NULL, 'F' ) ||
DECODE ( auf.vehiclerequirement,          0, NULL, 'B' ) ||
DECODE ( auf.routeid,       0, NULL, 'R' ) ||
DECODE ( auf.stationid,        0, NULL, 'H' ) ||
DECODE ( auf.clustid,      0, NULL, 'C' ) ||
DECODE ( auf.potentialid,      0, NULL, 'Z' ) || ')' )
INTO    sAName
FROM    pfv.order         auf,
pfv.plancaseversion pfv,
pfv.process         prz,
pfv.processart      pza
WHERE   auf.order_id  = iA_ID
AND     pfv.pf_id       = auf.pf_id
AND     pfv.process_id  = prz.process_id
AND     pza.art_id      = prz.art_id
GROUP BY  pza.code,prz.planlevel,prz.planyear,
prz.project,
pfv.version,
auf.ptest,
auf.diffdoc,
auf.offernr,
auf.demandmatrix,
auf.assign,
auf.stopoverresistance,
auf.vehicledim,
auf.vehiclerequirement,
auf.routeid,
auf.stationid,
auf.clustid,
auf.potentialid;
RETURN  sAName;
END;

but it probably costs not very much more than the expression without
COUNT(*) and without GROUP BY clause, which yields absolutely the
same result, but at least no one can object that the case of nonexistent
row is not accounted for :-)

The above construct is absurd and ugly but you should see the IF, THEN,
ELSE chain which I inherited in this function ;-). It was written by a guy
with a knack for modular programming. This function itself called another
function for a part of the OrderLongName, which then called a third
function for a further subpart of that part and the topmost function
was but one field in the most frequently used view in this app.
It was a killer app, indeed.

The strictly SQL-wise expression allowed me to compact all three
functions into one SQL query as stated above and even to replace
the call to this function in the view with this analytic expression,
with terrific impact upon the speed of queries. The most frequently
invoked query is now 30 times faster. A second most frequent only
18 times faster. And a seldom used query, which is apparently much
simpler (and runs in less time) than the other 2 only gets speeded up
by a factor of 2.5.

For some reason the CBO thrashes all the tables with full scans,
although it is obvious that indexes can be used for a much faster
execution. I manipulated the optimizer_index_cost_adj down to 1,
which caused a strange branching of QEP but no perceptible
improvement. The optimizer_max_permutations is at 80,000 and
complains that integer value is required if I increase it beyond that,
although the description says the range is 4..2^32 (4.3 billion).
It probably can't find a better QEP in so few permutations.

I'll probably have to dive into hints to straighten it out.
0

LVL 1

Expert Comment

ID: 12155688
I propose you change your function into:
FUNCTION getOrderLongName(iA_ID IN INTEGER)
RETURN  VARCHAR2        IS
sAName    VARCHAR2(100);
cursor c1 is
select  pza.code  ||  prz.planlevel  || '-' || prz.planyear ||
DECODE ( prz.project, NULL, '', '_' || RTRIM ( prz.project ) ) ||
' V' || LPAD ( TO_CHAR ( pfv.version ), 4, '0' ) ||
' A' || TO_CHAR ( iA_ID ) || ' (' ||
DECODE ( auf.ptest,     0, NULL, 'P' ) ||
DECODE ( auf.diffdoc,      0, NULL, 'D' ) ||
DECODE ( auf.offernr,         0, NULL, 'A' ) ||
DECODE ( auf.demandmatrix,    0, NULL, 'N' ) ||
DECODE ( auf.assign,           0, NULL, 'U' ) ||
DECODE ( auf.stopoverresistance, 1, 's', NULL ) ||
DECODE ( auf.vehicledim,             0, NULL, 'F' ) ||
DECODE ( auf.vehiclerequirement,          0, NULL, 'B' ) ||
DECODE ( auf.routeid,       0, NULL, 'R' ) ||
DECODE ( auf.stationid,        0, NULL, 'H' ) ||
DECODE ( auf.clustid,      0, NULL, 'C' ) ||
DECODE ( auf.potentialid,      0, NULL, 'Z' ) || ')' r_sAName
FROM    pfv.order           auf,
pfv.plancaseversion pfv,
pfv.process         prz,
pfv.processart      pza
WHERE   auf.order_id    = iA_ID
AND     pfv.pf_id       = auf.pf_id
AND     pfv.process_id  = prz.process_id
AND     pza.art_id      = prz.art_id
AND     rownum <= 1;
BEGIN
sAName    := NULL;
for rc1 in c1 loop
sAName := rc1.r_sAName;
end loop;
RETURN  sAName;
END;

If you have indexes on
1. auf.order_id,
2. pfv.pf_id,
3. prz.process_id and
4. pza.art_id
I think that it will be faster solution.

I think that you see difference. I mean that it is equivalent solution. Please check plans and cost these solutions.

Good Luck again
0

Author Comment

ID: 12156123
The query always returns at most 1 row because auf.order_id is unique
primary key. The proposed addition of cursor can't possibly make it faster,
in my opinion. Why I fussed so much about non-group group function is
I wanted a single-step query which can be integrated into a view as part
of the query defining the view, instead of having a function call as a field.
In that way the view becomes transparent to CBO and it can optimize the
plan of attack better.

And besides, my experience is that cursors are another brake and should
only be used when you can't think up a proper SQL query.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
###### Suggested Courses
Course of the Month8 days, 11 hours left to enroll