asked on

ora-00937 not single-group group function

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?

slightwv (䄆 Netminder)

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;

Kagiejot

In your query
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

ASKER CERTIFIED SOLUTION

Kagiejot

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dkrnic

ASKER

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.

Kagiejot

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

dkrnic

ASKER

I don't get your point.
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.