Last updated on SEPTEMBER 14, 2015
Applies to:PeopleSoft Enterprise PT PeopleTools - Version 8.52 and later
Information in this document applies to any platform.
*** Checked For Relevance On 14-September-2015 ***
When running a PS Query with an aggregate function and an expression with drilling URL used as a field the following error occurs:
"A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=8601, Message=[Microsoft][SQL Server Native Client 10.0][SQL Server]Each GROUP BY
expression must contain at least one column that is not an outer reference.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. (SQLSTATE 37000) 818 (50,380)"
The issue is specific to only MS SQL Server. The same Query is running fine on Oracle database.
The issue can be reproduced at will with the following steps:
1. Login to PIA
2. Navigate to People Tools > Security > Query Security > Query Access Manager
3. Search by QUERY_TREE_PT tree name
4. Click on Insert Child Record button
5. Enter the new record name: STDNT_AWRD_DISB
6. The new child record has been added successfully
7. Navigate to Reporting Tools > Query > Query Manager
8. Create a new Query (SB_DRILLING_URL)
9. Go to Expression tab and create a new expression using the Drilling URL Expression type
10. Click on Map Columns button and enable the check boxes on EMPLID, INSTITUTION, AID_YEAR
11. Use the expression as a field
12. Go to the Fields tab and add the aggregate SUM function to the NET_DISB_BALANCE field
13. See the Fields added to the new query created
14. See the Query SQL:
SELECT A.EMPLID, A.INSTITUTION, A.AID_YEAR, A.ITEM_TYPE, A.ACAD_CAREER,
A.DISBURSEMENT_ID, SUM( A.NET_DISB_BALANCE),
FROM PS_STDNT_AWRD_DISB A
GROUP BY A.EMPLID, A.INSTITUTION, A.AID_YEAR, A.ITEM_TYPE,
15. Go to Run tab
16. The error occurs.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms