Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners Click HERE | EMEA/APJ Click HERE |
01/10/2023 03:41 AM
Hi,
We have a use case in which, on the basis of statuskey of the user being imported, we want to set the termdate. If the statuskey is 0 then we have to set the termdate as system date else we have to let the termdate be as it is. We are using the following inline preprocessor json for it :
{
"ADDITIONALTABLES": {
"USERS": "SELECT username,statuskey,termdate FROM USERS"
},
"COMPUTEDCOLUMNS": [
"username","statuskey","termDate"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.termDate = case when NEWUSERDATA.statuskey = 0 then curdate() else CURRENTUSERS.termdate end"
]
}
But, when we upload a user through csv, we get the following error :
"ecm","2023-01-10T11:39:18.683+0000","2023-01-10T11:39:18.506284575Z stdout F 2023-01-10 11:39:18,506 [http-nio-8080-exec-4] ERROR services.ImportSAvDataUserService - Error while processing data: "
"ecm","2023-01-10T11:39:18.683+0000","2023-01-10T11:39:18.506308375Z stdout F com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'TEMPUSERS_14069.termDate' in 'field list'"
"ecm","2023-01-10T11:39:18.683+0000","2023-01-10T11:39:18.506313275Z stdout F at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)"
"ecm","2023-01-10T11:39:18.683+0000","2023-01-10T11:39:18.506316075Z stdout F at com.mysql.jdbc.Util.getInstance(Util.java:383)"
"ecm","2023-01-10T11:39:18.683+0000","2023-01-10T11:39:18.506318975Z stdout F at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)"
"ecm","2023-01-10T11:39:18.683+0000","2023-01-10T11:39:18.506321375Z stdout F at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)"
"ecm","2023-01-10T11:39:18.683+0000","2023-01-10T11:39:18.506335376Z stdout F at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)"
"ecm","2023-01-10T11:39:18.683+0000","2023-01-10T11:39:18.506338276Z stdout F at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)"
Kindly assist here.
Best Regards,
Varun
Solved! Go to Solution.
01/10/2023 03:58 AM
termDate Should be termdate
01/10/2023 05:08 AM
@rushikeshvartak - it is still not working, even after changing it to termdate.
Best Regards,
Varun
01/10/2023 05:37 AM - edited 01/10/2023 05:37 AM
{
"ADDITIONALTABLES": {
"USERS": "SELECT username,statuskey,termdate FROM USERS"
},
"COMPUTEDCOLUMNS": [
"username","statuskey","termdate "
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS SET NEWUSERDATA.termdate = case when NEWUSERDATA.statuskey = 0 then curdate() else CURRENTUSERS.termdate end where CURRENTUSERS.USERNAME=NEWUSERDATA.USERNAME"
]
}
01/10/2023 06:02 AM - edited 01/10/2023 06:03 AM
The highlighted where cause is not required
Rest of the query is fine. Thanks @rushikeshvartak .
However, this is a bit strange that it is required to mandatorily put a join between newuserdata and currentusers because in the situation where we do not put this join, Saviynt still shows in the logs that it has successfully created a temporary table on the basis of query provided in ADDITIONALTABLES.
Then a few log lines later, it refuses to identify the column which it has just created as a part of temp table creation. Log snippets below :
"ecm","2023-01-10T13:05:52.519+0000","2023-01-10T13:05:51.581997798Z stdout F 2023-01-10 13:05:51,581 [http-nio-8080-exec-6] DEBUG services.ImportSAvDataUserService - Executing Qry: "
"ecm","2023-01-10T13:05:52.519+0000","2023-01-10T13:05:51.582002198Z stdout F CREATE TABLE ssminlp.TEMPUSERS_14104"
"ecm","2023-01-10T13:05:52.519+0000","2023-01-10T13:05:51.582005198Z stdout F SELECT username,statuskey, CASE WHEN termdate IS NULL THEN CURDATE() ELSE TERMDATE END AS termdate FROM USERS"
"ecm","2023-01-10T13:05:52.519+0000","2023-01-10T13:05:51.582007798Z stdout F "
..............................
"ecm","2023-01-10T13:05:52.519+0000","2023-01-10T13:05:51.992894896Z stdout F com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'TEMPUSERS_14104.termdate' in 'field list'"
Not sure, how is the internal processing going on here.
Best Regards,
Varun
01/10/2023 06:25 AM
You case condition contains currentusers