We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Custom Query job failing for updating entitlement custom properties

shubhangsinha_
New Contributor III
New Contributor III

Hi All,

I have written a sql query to update entitlement attributes using CTE(Common Table expression) but I am getting a syntax error in the Saviynt logs which says- "groovy.sql.Sql.withBatch Error during batch execution: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'testtable  AS". Would you extremely helpful if any sort of alternative method/correction could be suggested. 

Query I am using - 

WITH testtable AS (select fieldname, fieldkey, minvalue, mxvalue, entitlement_values.ENTITLEMENT_VALUEKEY,ENTITLEMENT_VALUE from fields left join entitlement_objects
ON fields.fieldkey = entitlement_objects.field_key left join entitlement_values ON entitlement_objects.ENTITLEMENT_VALUEKEY = entitlement_values.ENTITLEMENT_VALUEKEY where ENTITLEMENT_VALUE like 'zd%' AND fieldkey=3
)
Update entitlement_values set customproperty10 = testtable.minvalue where testtable.ENTITLEMENT_VALUE = entitlement_values.entitlement_value and ENTITLEMENTTYPEKEY=13

 

15 REPLIES 15

rushikeshvartak
All-Star
All-Star

Query should start with insert/update , use joins


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Only doubt I have is that how will I update the customproperty of an entitlement with the minvalue (as it is coming from joins for 3 tables) and it does not have any specific name.

You can use inner query


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

I tried using the below query and the error I got is -"{"log":"25-Nov-2022 04:27:17.138 WARNING [quartzScheduler_Worker-6] groovy.sql.Sql.withBatch Error during batch execution: You can't specify target table 'entitlement_values' for update in FROM clause\n","stream":"stderr","time":"2022-11-25T04:27:17.138839634Z"}"

update entitlement_values set entitlement_values.customproperty30 = (select minvalue from fields left join entitlement_objects
ON fields.fieldkey = entitlement_objects.field_key left join entitlement_values ON entitlement_objects.ENTITLEMENT_VALUEKEY = entitlement_values.ENTITLEMENT_VALUEKEY where ENTITLEMENT_VALUE like 'zd%' AND fieldkey=3) where entitlement_values.ENTITLEMENTTYPEKEY=13

update entitlement_values e set e.customproperty30 = (select minvalue from fields left join entitlement_objects o
ON fields.fieldkey = o.field_key left join entitlement_values v ON o.ENTITLEMENT_VALUEKEY = v.ENTITLEMENT_VALUEKEY where e.ENTITLEMENT_VALUE like 'zd%' AND fieldkey=3) where e.ENTITLEMENTTYPEKEY=13


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Getting this in the logs-
{"log":"25-Nov-2022 04:49:02.466 WARNING [quartzScheduler_Worker-3] groovy.sql.Sql.withBatch Error during batch execution: You can't specify target table 'e' for update in FROM clause\n","stream":"stderr","time":"2022-11-25T04:49:02.466607259Z"}
2022-11-25, 10:19 am
ecm-worker

update fields f left join entitlement_objects o left join entitlement_values e set e.customproperty30 = f.minvalue where f.fieldkey = o.field and o.ENTITLEMENT_VALUEKEY = e.ENTITLEMENT_VALUEKEY and e.ENTITLEMENT_VALUE like 'zd%' AND f.fieldkey=3 and e.ENTITLEMENTTYPEKEY=13


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

groovy.sql.Sql.withBatch Error during batch execution: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set e.customproperty30 = f.minvalue where f.fieldkey = o.field and o.ENTITLEMENT' at line 1\n","stream":"stderr","time":"2022-11-25T05:21:20.749516532Z"}

update
fields f,
entitlement_objects o,
entitlement_values e
set
e.customproperty30 = f.minvalue
where
f.fieldkey = o.field
and o.ENTITLEMENT_VALUEKEY = e.ENTITLEMENT_VALUEKEY
and e.ENTITLEMENT_VALUE like 'zd%'
AND f.fieldkey = 3
and e.ENTITLEMENTTYPEKEY = 13


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Error Occured in customQueryExecution - true and exception - java.sql.BatchUpdateException: Unknown column 'o.field' in 'where clause'\n","stream":"stdout","time":"2022-11-25T05:33:00.787131894Z"}
2022-11-25, 11:03 am
{"log":"2022-11-25 05:33:00,787 [quartzScheduler_Worker-6] DEBUG jobs.CustomQueryJob - Error Occured for customQueryExecution - true\n","stream":"stdout","time":"2022-11-25T05:33:00.787395771Z"}


update
fields f,
entitlement_objects o,
entitlement_values e
set
e.customproperty30 = f.minvalue
where
f.fieldkey = o.field_key
and o.ENTITLEMENT_VALUEKEY = e.ENTITLEMENT_VALUEKEY
and e.ENTITLEMENT_VALUE like 'zd%'
AND f.fieldkey = 3
and e.ENTITLEMENTTYPEKEY = 13


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

shubhangsinha_
New Contributor III
New Contributor III

{"log":"2022-12-06 11:04:40,303 [quartzScheduler_Worker-1] DEBUG services.SaviyntCommonUtilityService - Error Occured in customQueryExecution - true and exception - java.sql.BatchUpdateException: Unknown column 'f.minvalue' in 'field list'\n","stream":"stdout","time":"2022-12-06T11:04:40.303249252Z"}

This is very basis error you should learn SQL

 

update
fields f,
entitlement_objects o,
entitlement_values e
set
e.customproperty30 = o.minvalue
where
f.fieldkey = o.field_key
and o.ENTITLEMENT_VALUEKEY = e.ENTITLEMENT_VALUEKEY
and e.ENTITLEMENT_VALUE like 'zd%'
AND f.fieldkey = 3
and e.ENTITLEMENTTYPEKEY = 13

 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

I did change the column name after I replied you and it worked , thank you so much for the help. Cheers! 

Please accept working solution to help others


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.