We have below query and currently upgrading db to 12.2.0.1. Query works well in 11.2.0.4 without group by but same query throws below error in 12c.
Query:
select * from v$version;
SELECT SUM( xxxx.yyyy)
FROM
(SELECT xxxx_trip_number,
xxxx_leg_indicator TRAVEL_SEGMENT ,
xxxx_destn_city_cd,
xxxx_dstn_cntry_cd,
start_dt,
end_dt,
SUM(xxxx_exp_amt) AMT
FROM ps_xxxx_ITN_AMT_VW
WHERE xxxx_trip_number='123456'
) xxxx;
output from 11G:
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SUM(xxxx.yyyy)
---------------------------------------
2060
Output from 12c:
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
Error at Command Line : 4 Column : 15
Error report -
SQL Error: ORA-00937: not a single-group group function
00937. 00000 - "not a single-group group function"
*Cause:
*Action:
Could you please help me to understand . am assuming group by is missing in query but wondering why it works in 11g?
Thanks in advance.