UserInline Preprocessor error

[varunpuri]

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

[rushikeshvartak]

termDate Should be termdate

[varunpuri]

@rushikeshvartak - it is still not working, even after changing it to termdate.

Best Regards,
Varun

[rushikeshvartak · Accepted solution]

{
"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"
]
}

[varunpuri]

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

[rushikeshvartak]

You case condition contains currentusers