Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 873
  • Last Modified:

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?
0
dkrnic
Asked:
dkrnic
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
KagiejotCommented:
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
 
KagiejotCommented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
dkrnicAuthor Commented:
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
 
KagiejotCommented:
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
 
dkrnicAuthor Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now