Question
Siemens Technology and Services Private Limited
IN
Last activity: 6 Dec 2018 9:58 EST
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:
But it fails and returns the following error:
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:
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: Proprietary information hidden) 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***
Accepted Solution
Siemens Technology and Services Private Limited
IN
Hi, it worked with a combination of the two answers marked as helpful. Thanks to both gentlemen.
Siemens Technology and Services Private Limited
IN
HI. Actually I missed the bracket when I pasted the query here. I have corrected it in the original post. So the issue is still present.
PEG
GB
I think you need to remove the "values as" from the statement.
Also, I'd be inclined to add a space before the "is not null" in the text: -
({.pxUserAgent}is not null)
Aaseya IT Solutions
IN
Can you try as per the following thread? Looks like you don't need "values as" keyword.
https://stackoverflow.com/questions/7323407/insert-select-statement-in-oracle-11g
basically, try the statement directly on DB first. following stmt worked
insert into DATA.CUSTOMERDATA_QA (appl_id, cust_fullname)
select APPL_ID,CUST_FULLNAME from DATA.FE_CA_CUSTOMERDATA
where appl_id = '12345';
EPAM Systems, Inc.
ES
Hi Uday,
Thanks for posting on PSC.
I feel there isn't a need for "Values As" keyword as per generic SQL syntax.
Try using,
insert into {class:SIE-Data-UsageReports}
( PXUSERAGENT ,
PXSAVEDATETIME ,
PXAPPLICATION,
PXREQUESTORTYPE,
PXSNAPSHOTTYPE,
PZINSKEY)
select
{.pxUserAgent} AS PXUSERAGENT,
{.pxSaveDateTime} AS PXSAVEDATETIME,
{.pxApplication} AS PXAPPLICATION,
{.pxRequestorType} AS PXREQUESTORTYPE,
{.pxSnapshotType} AS PXSNAPSHOTTYPE,
{.pzInsKey} AS PZINSKEY
from {class:Log-Usage}
where ({.pxUserAgent}is not null)
and ({.pxRequestorType}= 'BROWSER')
and ({.pxSnapshotType} = 'INTERVAL')
and (ROWNUM <= 50)
and ({.pxSaveDateTime} >= {TargetUsagePageSIE.pxSaveDateTime DateTime})
and ({.pxSaveDateTime} < {TargetUsagePageSIE.EndOfDay DateTime})
Also, try adding "AS <COLUMN_NAME>", if this isn't solve problem at your end, try without "AS <COLUMN_NAME>"
Hope it helps, kindly notify if it does by marking this answer as helpful/answered for larger Pega audience.
Regards,
Asif
Accepted Solution
Siemens Technology and Services Private Limited
IN
Hi, it worked with a combination of the two answers marked as helpful. Thanks to both gentlemen.