During the execution of Email DF getting below exception but we couldn't found where this below query has been formed
and was failed due to query was being formed wrongly. can someone provide some light here?
SQL: SELECT COUNT(*) AS "pyCount" FROM DATA.PR_DATA_CORR_EMAIL "PC0" WHERE
( "PC0"."STATUS" = ? AND "PC0"."OFFERCLASSNAME" = ? AND "PC0"."ACCOUNT" = ? AND "PC0"."PXAPPLICATION" = ? ) AND "PC0"."PXOBJCLASS" IN (? , ? ) UNION ALL SELECT "UN1"."PZINSKEY" AS "pzInsKey" , "UN1"."PARTITIONKEY" AS "PartitionKey" FROM DATA.PR_DATA_CORR_EMAIL_QUEUE "UN1" WHERE ( "UN1"."STATUS" = ? AND "UN1"."OFFERCLASSNAME" = ? AND
"UN1"."ACCOUNT" = ? AND "UN1"."PXAPPLICATION" = ? ) AND "UN1"."PXOBJCLASS" = ?
Pega OOTB agents are running at nodes fine.
java.lang.IllegalStateException: com.pega.dsm.dnode.api.DNodeException: Could not perform database dataset operation on Data-Corr-Email
at com.pega.dsm.dnode.impl.stream.DataObservableImpl$ObservableIterator.hasNext(DataObservableImpl.java:198)
at com.pega.dsm.dnode.impl.stream.DataObservableImpl$ObservableIterator.next(DataObservableImpl.java:229)
at com.pega.dsm.dnode.impl.stream.DataObservableImpl.first(DataObservableImpl.java:154)
at com.pega.dsm.dnode.api.dataflow.DataFlow.getNumberOfExpectedRecords(DataFlow.java:824)
at com.pega.dsm.dnode.impl.dataflow.service.PartitionCreator.createFor(PartitionCreator.java:104)
at com.pega.dsm.dnode.impl.dataflow.service.PartitionCreator.access$000(PartitionCreator.java:49)
at com.pega.dsm.dnode.impl.dataflow.service.PartitionCreator$1.run(PartitionCreator.java:66)
at com.pega.dsm.dnode.impl.dataflow.service.PartitionCreator$1.run(PartitionCreator.java:63)
at com.pega.dsm.dnode.util.PrpcRunnable.execute(PrpcRunnable.java:77)
at com.pega.dsm.dnode.impl.prpc.service.ServiceHelper$2.run(ServiceHelper.java:295)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1381)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:1124)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:931)
at com.pega.dsm.dnode.impl.prpc.service.ServiceHelper.executeInPrpcContextInternal(ServiceHelper.java:288)
at com.pega.dsm.dnode.impl.prpc.service.ServiceHelper.executeInPrpcContext(ServiceHelper.java:150)
at com.pega.dsm.dnode.impl.dataflow.service.PartitionCreator.createFor(PartitionCreator.java:63)
at com.pega.dsm.dnode.impl.dataflow.service.servicepulse.RunInitializationTask.preparePartitions(RunInitializationTask.java:230)
at com.pega.dsm.dnode.impl.dataflow.service.servicepulse.RunInitializationTask.asyncExecution(RunInitializationTask.java:144)
at com.pega.dsm.dnode.impl.dataflow.service.servicepulse.AsyncAssignedTask$1.run(AsyncAssignedTask.java:75)
at com.pega.dsm.dnode.impl.dataflow.service.servicepulse.AsyncAssignedTask$1.run(AsyncAssignedTask.java:64)
at com.pega.dsm.dnode.util.PrpcRunnable.execute(PrpcRunnable.java:77)
at com.pega.dsm.dnode.impl.prpc.service.ServiceHelper.executeInPrpcContextInternal(ServiceHelper.java:305)
at com.pega.dsm.dnode.impl.prpc.service.ServiceHelper.executeInPrpcContext(ServiceHelper.java:150)
at com.pega.dsm.dnode.impl.dataflow.service.servicepulse.AsyncAssignedTask.lambda$tryToExecuteInLock$0(AsyncAssignedTask.java:64)
at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)
at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:57)
at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at com.pega.dsm.dnode.util.PrpcRunnable$1.run(PrpcRunnable.java:69)
at com.pega.dsm.dnode.util.PrpcRunnable$1.run(PrpcRunnable.java:66)
at com.pega.dsm.dnode.util.PrpcRunnable.execute(PrpcRunnable.java:77)
at com.pega.dsm.dnode.impl.prpc.PrpcThreadFactory$PrpcThread.run(PrpcThreadFactory.java:164)
Caused by: com.pega.dsm.dnode.api.DNodeException: Could not perform database dataset operation on Data-Corr-Email
at com.pega.dsm.dnode.impl.dataset.database.DatabaseOperationsUtil.lambda$observableForDatabaseOperation$0(DatabaseOperationsUtil.java:78)
at com.pega.dsm.dnode.impl.stream.DataObservableImpl$SafeDataSubscriber.subscribe(DataObservableImpl.java:353)
at com.pega.dsm.dnode.impl.stream.DataObservableImpl.subscribe(DataObservableImpl.java:55)
at com.pega.dsm.dnode.impl.stream.DataObservableImpl$ObservableIterator.(DataObservableImpl.java:191)
at com.pega.dsm.dnode.impl.stream.DataObservableImpl.iterator(DataObservableImpl.java:149)
... 31 more
Caused by: com.pega.pegarules.pub.PRRuntimeException: Unable to query the database
at com.pega.dsm.dnode.util.NativeSQLBatchReader.init(NativeSQLBatchReader.java:205)
at com.pega.dsm.dnode.util.NativeSQLBatchReader.(NativeSQLBatchReader.java:115)
at com.pega.dsm.dnode.util.NativeSQLBatchReader.(NativeSQLBatchReader.java:94)
at com.pega.dsm.dnode.util.NativeSQLBatchReader.(NativeSQLBatchReader.java:98)
at com.pega.dsm.dnode.impl.dataset.database.DatabaseGetNumberOfRecordsOperation$1.executeExposedLogic(DatabaseGetNumberOfRecordsOperation.java:76)
at com.pega.dsm.dnode.impl.dataset.database.DatabaseOperationsUtil.lambda$observableForDatabaseOperation$0(DatabaseOperationsUtil.java:73)
... 35 more
Caused by: com.pega.pegarules.pub.database.DatabaseException: Unable to query the database: code: 1789 SQLState: 42000 Message: ORA-01789: query block has incorrect number of result columns
DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA-01789: query block has incorrect number of result columns
| SQL Code: 1789 | SQL State: 42000
From: (BVCYFHHQPWCRSQPPF5Y6T3CR5R7OVV5AOA)
SQL: SELECT COUNT(*) AS "pyCount" FROM DATA.PR_DATA_CORR_EMAIL "PC0" WHERE ( "PC0"."STATUS" = ? AND "PC0"."OFFERCLASSNAME" = ? AND "PC0"."ACCOUNT" = ? AND "PC0"."PXAPPLICATION" = ? ) AND "PC0"."PXOBJCLASS" IN (? , ? ) UNION ALL SELECT "UN1"."PZINSKEY" AS "pzInsKey" , "UN1"."PARTITIONKEY" AS "PartitionKey" FROM DATA.PR_DATA_CORR_EMAIL_QUEUE "UN1" WHERE ( "UN1"."STATUS" = ? AND "UN1"."OFFERCLASSNAME" = ? AND "UN1"."ACCOUNT" = ? AND "UN1"."PXAPPLICATION" = ? ) AND "UN1"."PXOBJCLASS" = ?
Caused by SQL Problems.
Problem #1, SQLState 42000, Error code 1789: java.sql.SQLSyntaxErrorException: ORA-01789: query block has incorrect number of result columns
at com.pega.pegarules.data.internal.access.ExceptionInformation.createAppropriateExceptionDueToDBFailure(ExceptionInformation.java:385)
at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:364)
at com.pega.dsm.dnode.util.NativeSQLBatchReader.init(NativeSQLBatchReader.java:197)
... 40 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-01789: query block has incorrect number of result columns
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)
at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:95)
at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:95)
at com.pega.pegarules.data.internal.store.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:532)
at com.pega.dsm.dnode.util.NativeSQLBatchReader.init(NativeSQLBatchReader.java:183)
... 40 more
Caused by: Error : 1789, Position : 0, Sql = SELECT COUNT(*) AS "pyCount" FROM DATA.PR_DATA_CORR_EMAIL "PC0" WHERE ( "PC0"."STATUS" = :1 AND "PC0"."OFFERCLASSNAME" = :2 AND "PC0"."ACCOUNT" = :3 AND "PC0"."PXAPPLICATION" = :4 ) AND "PC0"."PXOBJCLASS" IN (:5 , :6 ) UNION ALL SELECT "UN1"."PZINSKEY" AS "pzInsKey" , "UN1"."PARTITIONKEY" AS "PartitionKey" FROM DATA.PR_DATA_CORR_EMAIL_QUEUE "UN1" WHERE ( "UN1"."STATUS" = :7 AND "UN1"."OFFERCLASSNAME" = :8 AND "UN1"."ACCOUNT" = :9 AND "UN1"."PXAPPLICATION" = :10 ) AND "UN1"."PXOBJCLASS" = :11 , OriginalSql = SELECT COUNT(*) AS "pyCount" FROM DATA.PR_DATA_CORR_EMAIL "PC0" WHERE ( "PC0"."STATUS" = ? AND "PC0"."OFFERCLASSNAME" = ? AND "PC0"."ACCOUNT" = ? AND "PC0"."PXAPPLICATION" = ? ) AND "PC0"."PXOBJCLASS" IN (? , ? ) UNION ALL SELECT "UN1"."PZINSKEY" AS "pzInsKey" , "UN1"."PARTITIONKEY" AS "PartitionKey" FROM DATA.PR_DATA_CORR_EMAIL_QUEUE "UN1" WHERE ( "UN1"."STATUS" = ? AND "UN1"."OFFERCLASSNAME" = ? AND "UN1"."ACCOUNT" = ? AND "UN1"."PXAPPLICATION" = ? ) AND "UN1"."PXOBJCLASS" = ?, Error Msg = ORA-01789: query block has incorrect number of result columns
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
... 58 more
@SahajahanH0169 The error message “ORA-01789: query block has incorrect number of result columns” indicates that the SQL query you’re trying to execute is returning a different number of columns in different parts of a UNION or UNION ALL operation. In your case, the first part of the UNION ALL operation is returning one column (COUNT(*)), while the second part is returning two columns (PZINSKEY and PARTITIONKEY). This is not allowed in SQL. You need to ensure that all parts of a UNION or UNION ALL operation return the same number of columns. The query seems to be generated by Pega internally, possibly due to some configuration in your Email Data Flow. You might need to review your Data Flow configuration or consult with Pega Support for further assistance.
Please provide the INC is here if you log a support ticket via the MSP.
This is a GenAI-powered tool. All generated answers require validation against the provided references.
Getting DatabaseException : the Query has timed out, when trying to open any
Pega 8.7.1 - There was a problem getting a list: code: 0 SQLState: HY008