On SQL Server, Get CreateView Error Position: 0 Return: 8601 When Applying Campus Solutions 9.0 Bundle 44 (Doc ID 2228974.1)

Last updated on MARCH 17, 2017

Applies to:

PeopleSoft Enterprise CS Campus Community - Version 9 to 9 [Release 9]
PeopleSoft Enterprise CS Financial Aid - Version 9 to 9 [Release 9]
PeopleSoft Enterprise CS Recruiting and Admissions - Version 9 to 9 [Release 9]
PeopleSoft Enterprise CS Student Financials - Version 9 to 9 [Release 9]
PeopleSoft Enterprise CS Contributor Relations - Version 9 to 9 [Release 9]
Information in this document applies to any platform.

Symptoms

On Campus Solutions 9.0 when applying Bundle #44, an error occurs running CreateViews.SQL on SQL server. 

ERROR
-----------------------
Failed SQL stmt: CREATE VIEW PS_SAD_BPV_RSLT_VW (SAD_BPV_INDEX_ID, SAD_BPV_MSGUUID, SEQNUM, EXTERNAL_SYSTEM_ID, DESCR100, DESCR100_2, EXT_ORG_ID, SAD_SBB_STATUS, OPRID) AS (200,0)
SQL Error. Error Position: 0 Return: 8601 - [Microsoft][SQL Server Native Client 11.0][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

[Microsoft][SQL Server Native Clie
CREATE VIEW PS_SAD_BPV_RSLT_VW (SAD_BPV_INDEX_ID, SAD_BPV_MSGUUID, SEQNUM, EXTERNAL_SYSTEM_ID, DESCR100, DESCR100_2, EXT_ORG_ID, SAD_SBB_STATUS, OPRID) AS SELECT DISTINCT SAD_BPV_INDEX_ID , SAD_BPV_MSGUUID , SEQNUM , EXTERNAL_SYSTEM_ID , ( SELECT DESCR50 FROM PS_SAD_BPV_EXNM_VW EXNM WHERE EXNM.EXTERNAL_SYSTEM_ID = RSP1.EXTERNAL_SYSTEM_ID) , DESCR100 , (CASE WHEN RSP1.EXT_ORG_ID = ' ' THEN ( SELECT EXT_ORG_ID FROM PS_SAD_BPV_EXNM_VW EXNM WHERE EXNM.EXTERNAL_SYSTEM_ID = RSP1.EXTERNAL_SYSTEM_ID) ELSE RSP1.EXT_ORG_ID END) , (CASE WHEN RSP1.SAD_SBB_STATUS = ' ' THEN (CASE WHEN (( SELECT COUNT(1) FROM PS_SAD_BPV_EXNM_VW EXTKEY WHERE EXTKEY.EXTERNAL_SYSTEM_ID = RSP1.EXTERNAL_SYSTEM_ID) = 0) THEN 'N' ELSE 'E' END) ELSE RSP1.SAD_SBB_STATUS END), ( SELECT OPRID FROM PS_SAD_BPV_SBB_NLD WHERE SAD_BPV_INDEX_ID = RSP1.SAD_BPV_INDEX_ID) FROM PS_SAD_BPV_SBB_RSP RSP1 WHERE (RSP1.SAD_BPV_INDEX_ID) IN ( SELECT RSP2.SAD_BPV_INDEX_ID FROM PS_SAD_BPV_SBB_NLD RSP2 WHERE RSP2.SAD_BPV_TRANS_STAT = 'P' AND RSP2.SAD_BPV_TRANS_DTTM = ( SELECT MAX(RSP3...

Error: SQL execute error for CREATE VIEW PS_SAD_BPV_RSLT_VW (SAD_BPV_INDEX_ID, SAD_BPV_MSGUUID, SEQNUM, EXTERNAL_SYSTEM_ID, DESCR100, DESCR100_2, EXT_ORG_ID, SAD_SBB_STATUS, OPRID) AS SELECT DISTINCT SAD_BPV_INDEX_ID , SAD_BPV_MSGUUID , SEQNUM , EXTERNAL_SYSTEM_ID , ( SELECT DESCR50 FROM PS_SAD_BPV_EXNM_VW EXNM WHERE EXNM.EXTERNAL_SYSTEM_ID = RSP1.EXTERNAL_SYSTEM_ID) , DESCR100 , (CASE WHEN RSP1.EXT_ORG_ID = ' ' THEN ( SELECT EXT_ORG_ID FROM PS_SAD_BPV_EXNM_VW EXNM WHERE EXNM.EXTERNAL_SYSTEM_ID = RSP1.EXTERNAL_SYSTEM_ID) ELSE RSP1.EXT_ORG_ID END) , (CASE WHEN RSP1.SAD_SBB_STATUS = ' ' THEN (CASE WHEN (( SELECT COUNT(1) FROM PS_SAD_BPV_EXNM_VW EXTKEY WHERE EXTKEY.EXTERNAL_SYSTEM_ID = RSP1.EXTERNAL_SYSTEM_ID) = 0) THEN 'N' ELSE 'E' END) ELSE RSP1.SAD_SBB_STATUS END), ( SELECT OPRID FROM PS_SAD_BPV_SBB_NLD WHERE SAD_BPV_INDEX_ID = RSP1.SAD_BPV_INDEX_ID) FROM PS_SAD_BPV_SBB_RSP RSP1 WHERE (RSP1.SAD_BPV_INDEX_ID) IN ( SELECT RSP2.SAD_BPV_INDEX_ID FROM PS_SAD_BPV_SBB_NLD RSP2 WHERE RSP2.SAD_BPV_TRANS_STAT = 'P' AND RSP2.SAD_BPV_TR...




PeopleSoft Enterprise Campus Solutions Community  Support Blog  Information_Centers  Maintenance_Advisor 

 

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