Question

6
Replies
1250
Views
Uday Vell Member since 2017 3 posts
Siemens Technology and Services Private Limited
Posted: June 11, 2018
Last activity: December 6, 2018
Closed
Solved

SQL Select statement inside an Insert statement in SQL Connect Save tab

Hi,

I am trying to use the following SQL-Query in the Connect-SQL rule’s Save tab:

insert into {class:XXX-Data-UsageReports} (PXUSERAGENT , PXSAVEDATETIME , PXAPPLICATION, PXREQUESTORTYPE, PXSNAPSHOTTYPE, PZINSKEY) VALUES AS (

select {.pxUserAgent},
{.pxSaveDateTime},
{.pxApplication},
{.pxRequestorType},
{.pxSnapshotType},
{.pzInsKey}

from {class:Log-Usage}
where ({.pxUserAgent}is not null)
and ({.pxRequestorType}= 'BROWSER')
and ({.pxSnapshotType} = 'INTERVAL')
and (ROWNUM <= 50)
and ({.pxSaveDateTime} >= {TargetUsagePageXXX.pxSaveDateTime DateTime})
and ({.pxSaveDateTime} < {TargetUsagePageXXX.EndOfDay DateTime})
)

I am calling this rule in a RDB-Save activity method:

Image removed.

But it fails and returns the following error:

[MSG][Error in RDB-Save][STACK][com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 936 SQLState: 42000 Message: ORA-00936: missing expression DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression | SQL Code: 936 | SQL State: 42000 From: (H20F42A64AFB7204732FDAF133697976E:165.225.104.84) SQL: insert into PEGADATA.pr_XXX_Data_UsageReports (PXUSERAGENT , PXSAVEDATETIME , PXAPPLICATION, PXREQUESTORTYPE, PXSNAPSHOTTYPE, PZINSKEY) VALUES AS ( select ?, ?, ?, ?, ?, ? from PEGADATA.pr_perf_stats where (?is not null) and (?= 'BROWSER') and (? = 'INTERVAL') and (ROWNUM <= 50) and (? >= ?) and (? < ?) ) Caused by SQL Problems. Problem #1, SQLState 42000, Error code 936: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:262) at com.pega.pegarules.data.internal.access.ListSpec.list(ListSpec.java:491) at com.pega.pegarules.data.internal.access.DBQueryExecutor.executeRDB(DBQueryExecutor.java:114) at com.pega.pegarules.data.internal.access.DBQueryExecutor.executeRDB(DBQueryExecutor.java:66) at com.pega.pegarules.data.internal.access.Saver.saveRDB(Saver.java:1747) at com.pega.pegarules.data.internal.access.DatabaseImpl.saveRDB(DatabaseImpl.java:2734) at

If I just use the “Select …” part of the query above in the Browse tab and do an RBD-List. It works fine.

What's wrong with the Insert here ?

***Updated by moderator: Lochan to add Categories***

Data Integration
Moderation Team has archived post
Share this page LinkedIn