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:

Symptoms

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:

  WITH
  <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.

Changes

Cause

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