My Oracle Support Banner

While Applying PUM#31 on MS Sql Server Database, Views Failed with Error 'Incorrect Syntax near the Keyword 'Case' (Doc ID 2583713.1)

Last updated on JULY 22, 2020

Applies to:

PeopleSoft Enterprise SCM Inventory - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms



ISSUE 1
 
While creating C6010 build FSCM92 PI32 database on MS SQL Server, the
following
views failed with below error message
  
 The Views fixed in DB2 ZOS is failing in MSSQL.
 .
 ISSUE 1
 .
 Creating View IN_SUBITEM_RPT
  - SQL Error. Error Position: 0 Return: 8601 - [Microsoft][ODBC Driver 13
 for SQL Server][SQL Server]Incorrect syntax near the keyword 'CASE'.
 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near
 'TA'.
 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near
  
  CREATE VIEW PS_IN_SUBITEM_RPT (BUSINESS_UNIT, REQUEST_DATE, FISCAL_YEAR,
 YEAR_PERIOD, ORDER_NO, INV_ITEM_ID, SUB_ITEM_ID, UNIT_MEASURE_STD,
VENDOR_ID,
 QTY_SUB_ITEM, TOTAL_QTY, RATE_SUB) AS SELECT TA.BUSINESS_UNIT ,
 TA.REQUEST_DATE , TC.FISCAL_YEAR , TC.FISCAL_YEAR + '/' + CASE WHEN
 (TC.ACCOUNTING_PERIOD >= 10) THEN CONVERT(VARCHAR(40),TC.ACCOUNTING_PERIOD)
 ELSE '0' + CONVERT(VARCHAR(40),TC.ACCOUNTING_PERIOD) END CASE , TA.ORDER_NO
,
 TA.ORIG_INV_ITEM_ID , TA.INV_ITEM_ID , TE.UNIT_MEASURE_STD , TG.VENDOR_ID ,
 TA.QTY_REQUESTED_SUB , TB.TOTAL_QTY , ROUND(TA.QTY_REQUESTED_SUB /
 TB.TOTAL_QTY, 4) * 100 FROM ( SELECT T.DEMAND_SOURCE , T.SOURCE_BUS_UNIT ,
 T.BUSINESS_UNIT , T.REQUEST_DATE , T.ORDER_NO , T.ORIG_INV_ITEM_ID ,
 T.INV_I...
 Error: Unable to process create statement for IN_SUBITEM_RPT
 Views remaining: 23880
 .
 .
 .
 .
 ISSUE 2
 Creating View IN_SUBITEM_RPTC
  - SQL Error. Error Position: 0 Return: 8601 - [Microsoft][ODBC Driver 13
 for SQL Server][SQL Server]Invalid object name 'PS_IN_SUBITEM_RPT'.
 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could
not
 be prepared. (SQLSTATE 42000) 8180
  
  CREATE VIEW PS_IN_SUBITEM_RPTC (BUSINESS_UNIT, REQUEST_DATE, FISCAL_YEAR,
 YEAR_PERIOD, ORDER_NO, INV_ITEM_ID, SUB_ITEM_ID, UNIT_MEASURE_STD,
VENDOR_ID,
 QTY_SUB_ITEM, TOTAL_QTY, RATE_SUB, OPRCLASS) AS SELECT A.BUSINESS_UNIT ,
 A.REQUEST_DATE , A.FISCAL_YEAR , A.YEAR_PERIOD , A.ORDER_NO , A.INV_ITEM_ID
,
 A.SUB_ITEM_ID , A.UNIT_MEASURE_STD , A.VENDOR_ID , A.QTY_SUB_ITEM ,
 A.TOTAL_QTY , A.RATE_SUB , B.OPRCLASS FROM PS_IN_SUBITEM_RPT A ,
 PS_SEC_BU_CLS B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
 Error: Unable to process create statement for IN_SUBITEM_RPTC
 Views remaining: 3514
 .
 .
 ISSUE 3
 Creating View IN_SUBITEM_RPTO
  - SQL Error. Error Position: 0 Return: 8601 - [Microsoft][ODBC Driver 13
 for SQL Server][SQL Server]Invalid object name 'PS_IN_SUBITEM_RPT'.
 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could
not
 be prepared. (SQLSTATE 42000) 8180
  
  CREATE VIEW PS_IN_SUBITEM_RPTO (BUSINESS_UNIT, REQUEST_DATE, FISCAL_YEAR,
 YEAR_PERIOD, ORDER_NO, INV_ITEM_ID, SUB_ITEM_ID, UNIT_MEASURE_STD,
VENDOR_ID,
 QTY_SUB_ITEM, TOTAL_QTY, RATE_SUB, OPRID) AS SELECT A.BUSINESS_UNIT ,
 A.REQUEST_DATE , A.FISCAL_YEAR , A.YEAR_PERIOD , A.ORDER_NO , A.INV_ITEM_ID
,
 A.SUB_ITEM_ID , A.UNIT_MEASURE_STD , A.VENDOR_ID , A.QTY_SUB_ITEM ,
 A.TOTAL_QTY , A.RATE_SUB , B.OPRID FROM PS_IN_SUBITEM_RPT A , PS_SEC_BU_OPR
B
 WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
 Error: Unable to process create statement for IN_SUBITEM_RPTO
 .
 .
 ISSUE 4
 Creating View IN_SUBITEM_VW
  - SQL Error. Error Position: 0 Return: 8601 - [Microsoft][ODBC Driver 13
 for SQL Server][SQL Server]Incorrect syntax near the keyword 'CASE'.
 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near
 'TA'.
 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near
  
  CREATE VIEW PS_IN_SUBITEM_VW (BUSINESS_UNIT, REQUEST_DATE, FISCAL_YEAR,
 YEAR_PERIOD, ORDER_NO, INV_ITEM_ID, UNIT_MEASURE_STD, VENDOR_ID,
 QTY_SUB_ITEM, TOTAL_QTY, RATE_SUB) AS SELECT TA.BUSINESS_UNIT ,
 TA.REQUEST_DATE , TC.FISCAL_YEAR , TC.FISCAL_YEAR + '/' + CASE WHEN
 (TC.ACCOUNTING_PERIOD >= 10) THEN CONVERT(VARCHAR(40),TC.ACCOUNTING_PERIOD)
 ELSE '0' + CONVERT(VARCHAR(40),TC.ACCOUNTING_PERIOD) END CASE , TA.ORDER_NO
,
 TA.ORIG_INV_ITEM_ID , TE.UNIT_MEASURE_STD , TG.VENDOR_ID ,
 TA.QTY_REQUESTED_SUB , TB.TOTAL_QTY , ROUND(TA.QTY_REQUESTED_SUB /
 TB.TOTAL_QTY, 4) * 100 FROM ( SELECT T.DEMAND_SOURCE , T.SOURCE_BUS_UNIT ,
 T.BUSINESS_UNIT , T.REQUEST_DATE , T.ORDER_NO , T.ORIG_INV_ITEM_ID ,
 T1.CONVERSION_RATE , SUM(QTY_REQUESTED...
 Error: Unable to process create statement for IN_SUBITEM_VW
 .
 .
 (VIEWS-1) Views defined in the Application
 Designer and not found in the Database:
 Record Name Ownerid
 --------------- -------
 IN_SUBITEM_RPT DIN
 IN_SUBITEM_RPTC DIN
 IN_SUBITEM_RPTO DIN
 IN_SUBITEM_VW DIN



STEPS

The issue can be reproduced at will with the following steps:
1. Connect MS SQL Server database using below connection details
2. Run the Create view.DMS
3. You will see this issue
  


Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.