Click HERE to see how Saviynt Intelligence is transforming the industry. |
06/06/2024 03:42 AM
Hi,
We have implemented the preprocessor query as below where the requirement is that when termdate is equal to current date then populate users cp21.
Solved! Go to Solution.
06/06/2024 04:32 AM
Hi @poonammhetre ,
Can you try below:
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid=CURRENTUSERS.employeeid SET NEWUSERDATA.customproperty21 ='DISABLED' where NEWUSERDATA.TERMDATE != '' AND NEWUSERDATA.TERMDATE IS NOT NULL AND DATE(NEWUSERDATA.TERMDATE)=CURDATE()"
Let us know if this works!
if this helps your question, please consider selecting Accept As Solution and hit Kudos
06/06/2024 11:13 PM
@PremMahadikar Its not working, same error. Is this because of the date format we have at source side. date format is like this 2024-06-24T16:00:00Z
06/07/2024 03:53 PM
This is issue then!
Then below script should work:
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid=CURRENTUSERS.employeeid SET NEWUSERDATA.customproperty21 ='DISABLED' where NEWUSERDATA.TERMDATE != '' AND NEWUSERDATA.TERMDATE IS NOT NULL AND DATE_FORMAT(NEWUSERDATA.TERMDATE,'%Y-%m-%d')=CURDATE()"
If this helps your question, please consider selecting Accept As Solution and hit Kudos
06/08/2024 08:48 AM
{
"ADDITIONALTABLES": {
"USERS": "SELECT employeeid,customproperty21,termdate,statuskey from users"
},
"COMPUTEDCOLUMNS": [
"customproperty21"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid SET NEWUSERDATA.customproperty21 = 'DISABLED' WHERE NEWUSERDATA.TERMDATE != '' AND NEWUSERDATA.TERMDATE IS NOT NULL AND DATE(CONVERT_TZ(STR_TO_DATE(NEWUSERDATA.TERMDATE, '%Y-%m-%dT%H:%i:%sZ'), '+00:00', '+00:00')) = DATE(CURDATE())"
]
}
06/26/2024 06:36 AM
@rushikeshvartak Thanks a lot. Its working.