Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

UserInline Preprocessor error

varunpuri
Regular Contributor
Regular Contributor

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

5 REPLIES 5

rushikeshvartak
All-Star
All-Star

termDate Should be termdate


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

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

Best Regards,
Varun

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


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

The highlighted where cause is not required

varunpuri_0-1673358809843.png
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

 

You case condition contains currentusers


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