E-QR: PS Query With Aggregate Function And Drilling URL Expression Used As A Field Is Failing Out (Doc ID 1546909.1)

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 ***

Symptoms

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.

Steps:
-------
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),
'psp///c/PACKAGE_AID.AWARD_ACTIVITY.GBL?Page=STDNT_AWRD_ACTV&Action=U:A.EMPLID
:A.INSTITUTION:A.AID_YEAR'
  FROM PS_STDNT_AWRD_DISB A
  GROUP BY  A.EMPLID,  A.INSTITUTION,  A.AID_YEAR,  A.ITEM_TYPE,
A.ACAD_CAREER,  A.DISBURSEMENT_ID,
'psp///c/PACKAGE_AID.AWARD_ACTIVITY.GBL?Page=STDNT_AWRD_ACTV&Action=U:A.EMPLID
:A.INSTITUTION:A.AID_YEAR' 

15. Go to Run tab

16. The error occurs.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms