dkrnic
asked on
ora-00937 not single-group group function
for some reason a SELECT of a DECODE mixing the built-in aggregate function COUNT(*) with single-valued fields
causes ora-00937. Adding a GROUP BY clause, as suggested by Henka, only causes a different error to be produced
- ora-00979.
Now slightwv claims it always works for constants and literals (i.e. non-tabular values), which is precisely why it is
useless, even if it does work. His/her/its second suggestion to split the query into 2, by first assigning COUNT(*) INTO a
local variable and then using this var in the desired query is the one I ran away from.
Unfortunately, this site does not allow to continue discussing a problem in a thread, so each time I get an unsatisfactory
reply I have to post a new question as though there never were any previous questions and answers. There's a hyperlink
"Feedback" above the reply but it leads to an editor to provide a Feedback to the question, not to the reply. That's
extremely confusing. It may be that my "Feedback" would land under the same thread, but why can't I see the whole
goddamn thread while I'm feeding back my response is beyond me.
Anyways, my initial Q is here: https://www.experts-exchange.com/questions/21136044/ora-00937-not-single-group-group-function.html
As you can see, the problem arose when I tried to get rid of BASIC programming with IF, ELSE and END, and
express the function as a pure SQL-Query. A purely SQL-wise expression in a function has the advantage that it can
relatively easily be transplanted into a VIEW to get rid of costly function invocation. Once a VIEW is independent of any
functions, CBO can work out severely improved QEPs. I've seen a 30-fold speed-up in such cases. By splitting the query into two queries, you lose this capability. Next answer, please.
Just in case someone else would like to see my second question and slightwv's reply, it is here:
https://www.experts-exchange.com/questions/21137692/ora-00937-not-single-group-group-function.html
I'll obviously have to up the ante now in the hope an SQL-guru might bite. The query is typical replacement for a number
of functions in which a SELECT COUNT(*) INTO LocalVar FROM aTable WHERE someCondition; precedes an
IF LocalVar > 0 THEN doThis END construct. In most cases, this precaution is totally superfluous, because the query yields
NULL value anyway if someCondition is not satisfied. However, testing explicitly for the presence of a row satisfying
someCondition, tends to be perceived by lay managementry as somehow better guaranteeing conformance to the old
functional school. To put it bluntly, it has to be there so that I don't have to low-brow my peers and bosses.
Now, I would also grant all the points to someone who can explain why it's impossible, if that's the case.
causes ora-00937. Adding a GROUP BY clause, as suggested by Henka, only causes a different error to be produced
- ora-00979.
Now slightwv claims it always works for constants and literals (i.e. non-tabular values), which is precisely why it is
useless, even if it does work. His/her/its second suggestion to split the query into 2, by first assigning COUNT(*) INTO a
local variable and then using this var in the desired query is the one I ran away from.
Unfortunately, this site does not allow to continue discussing a problem in a thread, so each time I get an unsatisfactory
reply I have to post a new question as though there never were any previous questions and answers. There's a hyperlink
"Feedback" above the reply but it leads to an editor to provide a Feedback to the question, not to the reply. That's
extremely confusing. It may be that my "Feedback" would land under the same thread, but why can't I see the whole
goddamn thread while I'm feeding back my response is beyond me.
Anyways, my initial Q is here: https://www.experts-exchange.com/questions/21136044/ora-00937-not-single-group-group-function.html
As you can see, the problem arose when I tried to get rid of BASIC programming with IF, ELSE and END, and
express the function as a pure SQL-Query. A purely SQL-wise expression in a function has the advantage that it can
relatively easily be transplanted into a VIEW to get rid of costly function invocation. Once a VIEW is independent of any
functions, CBO can work out severely improved QEPs. I've seen a 30-fold speed-up in such cases. By splitting the query into two queries, you lose this capability. Next answer, please.
Just in case someone else would like to see my second question and slightwv's reply, it is here:
https://www.experts-exchange.com/questions/21137692/ora-00937-not-single-group-group-function.html
I'll obviously have to up the ante now in the hope an SQL-guru might bite. The query is typical replacement for a number
of functions in which a SELECT COUNT(*) INTO LocalVar FROM aTable WHERE someCondition; precedes an
IF LocalVar > 0 THEN doThis END construct. In most cases, this precaution is totally superfluous, because the query yields
NULL value anyway if someCondition is not satisfied. However, testing explicitly for the presence of a row satisfying
someCondition, tends to be perceived by lay managementry as somehow better guaranteeing conformance to the old
functional school. To put it bluntly, it has to be there so that I don't have to low-brow my peers and bosses.
Now, I would also grant all the points to someone who can explain why it's impossible, if that's the case.
Helena Marková
I think that there was no need of opening a new questions. You can put your comments to the original question ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's an interesting twist, but the problem has been resolved
in a different thread. Basically the above example is an oversimplification
of the actual query, which involves some dozens of decodes and the
way to make it work with count(*) without causing the error is to
include all of the fields in the GROUP BY clause.
in a different thread. Basically the above example is an oversimplification
of the actual query, which involves some dozens of decodes and the
way to make it work with count(*) without causing the error is to
include all of the fields in the GROUP BY clause.
ASKER
Thanks, Steve.
your solution is elegant.
Since I already came to terms with the bug,
I've fogot that a thread has to be closed some time.
But not without credit, where credit is due.
your solution is elegant.
Since I already came to terms with the bug,
I've fogot that a thread has to be closed some time.
But not without credit, where credit is due.