Solved

ora-00937 not single-group group function

Posted on 2004-09-20
6
852 Views
Last Modified: 2013-12-11
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
Comment
Question by:dkrnic
  • 3
  • 2
6 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
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

by:Kagiejot
ID: 12154059
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
0
 
LVL 1

Accepted Solution

by:
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:dkrnic
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

by:Kagiejot
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

by:dkrnic
ID: 12156123
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now