After the upgrade i got a message from the guys who run the batch jobs that one of those jobs
crashed with the error ORA-00937: not a single-group group function
The same job ran fine on the version 10.2.0.4
The error occured in this statement
Cursor crb_leidend_b is
select
group_nr,
max(to_number(relnr)) relnr
from (
select group_nr, relnr, score
from crb_groepsscore_b a0
where exists (
select to_char(a1.group_nr)||to_char(max(a1.score))
from crb_groepsscore_b a1
where a0.group_nr = a1.group_nr
and a0.score = a1.score
)
) group by group_nr;
The select statement in red was the cause of the error witch makes sense because in that
statement the funcion max is used so then you have to use the goup by clause.
In Oracle 10gr2 or below Oracle Optimizer doesn't have a problem with that but as of Oracle 11gr2
the optimizer used stricter rules an gives the error ORA-00937: not a single-group group function.
which is logical.
How to solve the problem.
Well of course you can add the group by clause in the sub query
Cursor crb_leidend_b is
select
group_nr,
max(to_number(relnr)) relnr
from (
select group_nr, relnr, score
from crb_groepsscore_b a0
where exists (
select to_char(a1.group_nr)||to_char(max(a1.score))
from crb_groepsscore_b a1
where a0.group_nr = a1.group_nr
and a0.score = a1.score
group by group_nr,score
)
) group by group_nr;
But th subquery is used for the where exsist and this means it only want to know if the value exsist or not. So there is no need at all for the select to_char(a1.group_nr)||to_char(max(a1.score)). It doesn't return the value only if it exsist.or not.
So the other solution would be
Cursor crb_leidend_b is
select
group_nr,
max(to_number(relnr)) relnr
from (
select group_nr, relnr, score
from crb_groepsscore_b a0
where exists (
select 1
from crb_groepsscore_b a1
where a0.group_nr = a1.group_nr
and a0.score = a1.score
)
) group by group_nr
Thats it
No comments:
Post a Comment