E-PC: SQL Error On SQLExec with SQL Object That Has DB2 Specific Syntax Using the WITH Clause that contains sub-queries
(Doc ID 2683216.1)
Last updated on AUGUST 11, 2022
Applies to:PeopleSoft Enterprise PT PeopleTools - Version 8.58 to 8.58 [Release 8.4]
Information in this document applies to any platform.
SQL Error on SQLExec with SQL Object that Has DB2 Specific Syntax Using the WITH Clause that contains sub-queries.
Issue reported on PT 8.58.03, on DB2 for z/OS.
The issue can be reproduced at will with the following steps:
1- Create Peoplecode that calls the SQLExec with the following statement:
SQLExec(SQL.<SQLID>, <list of parameters>);
2- The SQLID has DB2 Specific Syntax Using the WITH Clause, such as:
<alias1> AS ( <SELECT statement with more than 1 sub-query> - e.g.: using statements such as EXISTS or MAX),
<alias2> AS ( <another SELECT statement with more than 1 sub-query> - e.g.: using statements such as EXISTS or MAX> ),
SELECT DISTINCT <list of fields>
FROM <alias2> A LEFT OUTER JOIN <alias1> B ON A.<joinField1>=B.<joinField1>
AND A.<joinField2> =B.<joinField2>
AND A.<joinField3> =B.<joinField3>
AND A.<joinFieldn> =B.<joinFieldn>
WHERE <WHERE clause, referencing aliases A and B>
3- Execute the PeopleCode in PIA.
4- Error is given:
SQLExec: <x> select list items in parameter list, but SQL has 0 select columns.
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