Preprocessor - Table 'ssminlp.currentusers' doesn't exist

[flegare]

The authoritative source feeds the manager to Saviynt with the manager's email address.  We tried to get the mapping through the import preprocessor with a syntax such as:

{
	"ADDITONALTABLES": {
        "USERS": "select USERKEY, USERNAME, EMAIL from USERS"
    },
    "COMPUTEDCOLUMNS": [
        "manager"
    ],
    "PREPROCESSQUERIES": [
        "UPDATE NEWUSERDATA SET manager = (select CURRENTUSERS.USERNAME from CURRENTUSERS where NEWUSERDATA.customproperty24 = CURRENTUSERS.email)"
    ]
}

However, we are constantly getting an exception: 

Failed to execute: UPDATE TEMPNEWUSERS_5334 SET manager = (select CURRENTUSERS.USERNAME from CURRENTUSERS where TEMPNEWUSERS_5334.customproperty24 = CURRENTUSERS.email) because: Table 'ssminlp.currentusers' doesn't exist"

Any idea as to what is missing for the currentusers table to be acknowledged?

Thanks!

[Dhruv_S · Accepted solution]

Hi @flegare 

Could you please refer to the syntax similar to the one in the below post and modify your query accordingly. Let me know if this helps resolve the issue.

Solved: Getting error "Column count doesn't match value co... - Saviynt Forums - 70830

Regards,

Dhruv Sharma

[flegare]

Hi @Dhruv_S ,

Correct me if I am wrong but this statement is slightly different than what we are attempting to do.  That being said, I did attempt something similar and the statement fails at index creation  

MODIFYUSERJSON:

{
    "ADDITONALTABLES": {
        "USERS": "SELECT userkey,email FROM USERS"
    },
	"COMPUTEDCOLUMNS": [
        "customproperty24",
        "lastname",
        "displayname",
        "username"
    ],
    "TABLEINDEXES": {
		"currentusers": [
			"email"
		]
	},
	"PREPROCESSQUERIES": [
        "UPDATE NEWUSERDATA SET CUSTOMPROPERTY24=LOWER(CUSTOMPROPERTY24)"
    ]
}

Here is the log information:
"2024-01-31T15:25:48.280+00:00","ecm","","","","2024-01-31T15:25:47.426537334Z stdout F 2024-01-31 15:25:47,426 [http-nio-8080-exec-108] DEBUG services.ImportSAvDataUserService - Start adding indexes for temp tables"
"2024-01-31T15:25:48.280+00:00","ecm","","","","2024-01-31T15:25:47.426592579Z stdout F 2024-01-31 15:25:47,426 [http-nio-8080-exec-108] DEBUG services.ImportSAvDataUserService - Adding index using query: ALTER TABLE ssminlp.currentusers ADD INDEX email_TMPIDX (email ASC)"
"2024-01-31T15:25:48.280+00:00","ecm","","","","2024-01-31T15:25:47.427311542Z stderr F 31-Jan-2024 15:25:47.427 WARNING [http-nio-8080-exec-108] groovy.sql.Sql.executeUpdate Failed to execute: ALTER TABLE ssminlp.currentusers ADD INDEX email_TMPIDX (email ASC) because: Table 'ssminlp.currentusers' doesn't exist"

Not sure where we are going wrong with this...

Thanks a lot for your help!

[flegare · Accepted solution]

Hi @Dhruv_S ,

I am starting to wonder if this is not a version-dependent feature.  I have access to another tenant where I was able to run the following preprocessor statement successfully.  Do you have any way to check if v23.8 would behave differently than 23.11 in this regard?

{
    "ADDITIONALTABLES": {
        "USERS": "Select username,email from users"
    },
    "COMPUTEDCOLUMNS": [
        "manager"
    ],
    "TABLEINDEXES": {
        "currentusers": [
            "email"
        ]
    },
    "PREPROCESSQUERIES": [
        "UPDATE NEWUSERDATA SET manager = (select CURRENTUSERS.USERNAME from CURRENTUSERS where NEWUSERDATA.CUSTOMPROPERTY24 = CURRENTUSERS.email)"
    ]
}

 Thanks!

Francois

[flegare]

@Dhruv_S , nevermind all this.  Issue resolved by using the syntax provided in the previous post.  Function works on both 23.8 and 23.11

Thanks anyways!

[rushikeshvartak]

This should work irrespective of version

{
    "ADDITIONALTABLES": {
        "USERS": "Select username,email from users"
    },
    "COMPUTEDCOLUMNS": [
        "manager"
    ],
    "TABLEINDEXES": {
        "currentusers": [
            "email"
        ]
    },
    "PREPROCESSQUERIES": [
        "UPDATE NEWUSERDATA SET manager = (select CURRENTUSERS.USERNAME from CURRENTUSERS where NEWUSERDATA.CUSTOMPROPERTY24 = CURRENTUSERS.email)"
    ]
}