DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-00937: not a single-group group function

  1. #1
    Join Date
    Feb 2001
    Posts
    100

    Question ORA-00937: not a single-group group function

    Hi All,

    I got the following errror when calling a function a select.

    ERROR at line 1:
    ORA-00937: not a single-group group function
    ORA-06512: at "PUSER.USERQTOTALNOOFUSRS", line 8
    ORA-06512: at line 1


    PROCEDURE USERQTOTALNOOFUSRS
    (
    RCT1 IN OUT GLOBALPKG.RCT1
    )
    AS
    BEGIN

    OPEN RCT1 FOR
    SELECT
    COUNT(TUSER.UNIQUEID) - 2 AS COUNTOFUNIQUEID,
    USERQTOTALNOOFUSRS_Function0(TORGANIZATION.ORGUNIQUEID) AS CountOfDecomm
    FROM TUSER,
    TORGANIZATION
    WHERE TUSER.ORGANIZATIONID = TORGANIZATION.ORGUNIQUEID
    AND TORGANIZATION.ORGNAME = 'TENROX' ;
    END;


    FUNCTION USERQTOTALNOOFUSRS_Function0
    (
    TORGANIZATION_ORGUNIQUEID INTEGER
    )
    RETURN INTEGER
    AS
    AssignmentVariable INTEGER;

    BEGIN
    SELECT COUNT(TUSER.UNIQUEID) INTO AssignmentVariable
    FROM TUSER,
    TORGANIZATION
    WHERE TUSER.ORGANIZATIONID = TORGANIZATION_ORGUNIQUEID
    AND (TORGANIZATION.ORGNAME = 'TENROX')
    AND (TUSER.USERACCESSSTATUS = 4);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    BEGIN
    AssignmentVariable := 0;
    RETURN AssignmentVariable;
    END;

    WHEN TOO_MANY_ROWS THEN
    BEGIN
    AssignmentVariable := 0;
    RETURN AssignmentVariable;
    END;
    RETURN AssignmentVariable;
    END;

    What's wrong.

    Thanks in advance

  2. #2
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    511

    Re: ORA-00937: not a single-group group function

    Originally posted by Hakimca
    Hi All,

    I got the following errror when calling a function a select.

    ERROR at line 1:
    ORA-00937: not a single-group group function
    ORA-06512: at "PUSER.USERQTOTALNOOFUSRS", line 8
    ORA-06512: at line 1


    SELECT COUNT(TUSER.UNIQUEID) - 2 AS COUNTOFUNIQUEID,
    USERQTOTALNOOFUSRS_Function0(TORGANIZATION.ORGUNIQUEID) AS CountOfDecomm
    FROM TUSER,TORGANIZATION
    WHERE TUSER.ORGANIZATIONID = TORGANIZATION.ORGUNIQUEID
    AND TORGANIZATION.ORGNAME = 'TENROX' ;
    ...............
    What's wrong.

    Thanks in advance
    You are missing the group by clause.

    Add GROUP BY USERQTOTALNOOFUSRS_Function0(TORGANIZATION.ORGUNIQUEID)
    to the select statement.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  3. #3
    Join Date
    Feb 2001
    Posts
    100
    Thank you very much Kris.

    But I got an other error message.

    SQL> execute USERQTOTALNOOFUSRS(:c)

    PL/SQL procedure successfully completed.

    SQL> print c
    ERROR:
    ORA-06503: PL/SQL: Function returned without value
    ORA-06512: at "PUSER.USERQTOTALNOOFUSRS_FUNCTION0", line 33
    ORA-06512: at line 1



    no rows selected

    SQL>


    Even I modified the function USERQTOTALNOOFUSRS_FUNCTION0 as below


    FUNCTION USERQTOTALNOOFUSRS_Function0
    (
    TORGANIZATION_ORGUNIQUEID INTEGER
    )
    RETURN INTEGER
    AS
    AssignmentVariable INTEGER;

    BEGIN
    AssignmentVariable := 0;
    SELECT COUNT(TUSER.UNIQUEID) INTO AssignmentVariable
    FROM TUSER,
    TORGANIZATION
    WHERE TUSER.ORGANIZATIONID = TORGANIZATION_ORGUNIQUEID
    AND (TORGANIZATION.ORGNAME = 'TENROX')
    AND (TUSER.USERACCESSSTATUS = 4);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    BEGIN
    AssignmentVariable := 0;
    RETURN AssignmentVariable;
    END;
    WHEN TOO_MANY_ROWS THEN
    BEGIN
    NULL;
    RETURN AssignmentVariable;
    END;
    WHEN OTHERS THEN
    BEGIN
    AssignmentVariable := 0;
    RETURN AssignmentVariable;
    END;
    RETURN AssignmentVariable;
    END;

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    I dono why u need NO_DATA_FOUND.

    Just scan/go thru ur function again.

    Try this...

    FUNCTION USERQTOTALNOOFUSRS_Function0
    (
    TORGANIZATION_ORGUNIQUEID INTEGER
    )
    RETURN INTEGER
    AS
    AssignmentVariable INTEGER;
    BEGIN
    AssignmentVariable := 0;

    SELECT COUNT(TUSER.UNIQUEID) INTO AssignmentVariable
    FROM TUSER,
    TORGANIZATION
    WHERE TUSER.ORGANIZATIONID = TORGANIZATION_ORGUNIQUEID
    AND (TORGANIZATION.ORGNAME = 'TENROX')
    AND (TUSER.USERACCESSSTATUS = 4);

    RETURN AssignmentVariable;
    END;
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width