-
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
-
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
-
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;
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|