You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Query Expression: Using an aggregate in a CASE statement

2 replies [Last post]
Guest's picture
Guest

Hi All, I'm trying to create a query with the PeopleSoft Query Builder tool that tells the user if someone has adequate role security access to function properly within PeopleSoft. The query uses an aggregate expression to count the number of roles a user has (included as a column in the query). If the user has the maximum number of roles required, the query should display message 1: "PS Security is sufficient". Anything less than the max number of rows should show message 2: "Security Update Required". The query I created already has a few records left joined to each other to show if the person has a userid. I'm currently trying to create a CASE expression to say "if the count in the aggregate column = 4 (max number of roles) then show message 1, else, show message 2".

My problem is when I attempt to build an expression that references the aggregate column using this code:

(CASE WHEN (count(C.ROLEUSER) = '4') THEN 'message 1' ELSE 'message 2' END)

I get the following error message

"SQL error. Stmt #: 5683 Error Position: 409 Return: 934 - ORA-00934: group function is not allowed here
A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=934, Message=ORA-00934: group function is not allowed here (50,380)"

I know it has to do with properly referencing the count aggregate column, I'm just not sure how to name the column so it is recognized by PeopleSoft... I renamed the field name for the aggregate column and tried using that name as well in the expression, but it is not recognized. Any suggestions would be greatly appreciated!!! Many thx.

Guest's picture
Guest (not verified)
Re: Query Expression: Using an aggregate in a CASE statement

Just FYI, I figured out why I was getting the error message. PeopleSoft read the "Count(C.ROLEUSER)" part as an aggregate. I had to check the "Aggregate Function" checkbox under the Edit Expression Properties page for this expression. That fixed the problem. Hope this helps others! Thx everyone.

Lepa's picture
User offline. Last seen 1 year 24 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Query Expression: Using an aggregate in a CASE statement

Thank you for sharing your findings with us. I appreciate this!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog