EAP: SQL error when clicking on magnifying glass for SpeedChart field on Voucher ( SQL Server / Sybase issue) (Doc ID 1571663.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

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

Symptoms

When clicking on the magnifying glass for the SpeedChart field on the Voucher > Invoice Information page, an error occurs.

SQL SERVER ERROR
A SQL error occurred. please consult your system log for details.

SELECT SETID, SPEEDCHART_KEY, DESCR, OPRID, OPRCLASS, EFFDT, EFF_STATUS FROM ( SELECT A.SETID, A.SPEEDCHART_KEY, A.DESCR, A.OPRID, A.OPRCLASS, A.EFFDT, A.EFF_STATUS FROM PS_SPEEDCHART_HDR A WHERE (A.OPRID = 'VP1' OR A.OPRID = ' ') AND (A.OPRCLASS = 'ALLPAGES' OR A.OPRCLASS = ' ') AND A.EFFDT = (SELECT MAX(I.EFFDT) FROM PS_SPEEDCHART_HDR I WHERE I.SETID = A.SETID AND I.SPEEDCHART_KEY = A.SPEEDCHART_KEY AND I.OPRID = A.OPRID AND I.OPRCLASS = A.OPRCLASS AND I.EFFDT <= '2013-07-05') AND A.EFF_STATUS = 'A' ) WHERE SETID=:1 AND EFF_STATUS<>'I' ORDER BY 1,2,3,4,5,6,7
PSAPPSRV.1824 (1095) 1-7480 09.18.06 0.000000 Cur#1.1824.FMSDMO RC=0 Dur=0.000000 Bind-1 type=2 length=5 value=SHARE
PSAPPSRV.1824 (1095) 1-7481 09.18.06 0.002000 Cur#1.1824.FMSDMO RC=8601 Dur=0.002000 EXE
PSAPPSRV.1824 (1095) 1-7482 09.18.06 0.000000 Cur#1.1824.FMSDMO RC=0 Dur=0.000000 RCD rtncd=8601
PSAPPSRV.1824 (1095) 1-7483 09.18.06 0.000000 Cur#1.1824.FMSDMO RC=0 Dur=0.000000 EPO error pos=0
PSAPPSRV.1824 (1095) 1-7484 09.18.06 0.000000 Cur#0.1824.notSamTran RC=0 Dur=0.000000 XER rtncd=0 msg=[Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'WHERE'.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s)

SYBASE ERROR
File: /vob/peopletools/src/psppr/ramget.cppSQL error. Stmt #: 656  Error Position: 0  Return: 11753 - The derived table expression is missing a correlation name. Check derived table syntax in the Reference Manual.
Failed SQL stmt: SELECT SETID, SPEEDCHART_KEY, DESCR, OPRID, OPRCLASS, EFFDT, EFF_STATUS FROM ( SELECT A.SETID, A.SPEEDCHART_KEY, A.DESCR, A.OPRID, A.OPRCLASS, A.EFFDT, A.EFF_STATUS FROM PS_SPEEDCHART_HDR A WHERE (A.OPRID = 'VP1' OR A.OPRID = ' ') AND (A.OPRCLASS = 'ALLPAGES' OR A.OPRCLASS = ' ') AND A.EFFDT = (SELECT MAX(I.EFFDT) FROM PS_SPEEDCHART_HDR I WHERE I.SETID = A.SETID AND I.SPEEDCHART_KEY = A.SPEEDCHART_KEY AND I.OPRID = A.OPRID AND I.OPRCLASS = A.OPRCLASS AND I.EFFDT <= '2013-04-26') AND A.EFF_STATUS = 'A' ) WHERE SETID=:1 AND EFF_STATUS<>'I' ORDER BY 1,2,3,4,5,6,7

STEPS
1.  On the Voucher > Invoice Information page, click on the magnifying glass for SpeedChart
2.  Above 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