We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

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

flegare
Regular Contributor II
Regular Contributor II

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!

5 REPLIES 5

Dhruv_Sharma
Saviynt Employee
Saviynt Employee

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
Regular Contributor II
Regular Contributor II

Hi @Dhruv_Sharma ,

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
Regular Contributor II
Regular Contributor II

Hi @Dhruv_Sharma ,

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
Regular Contributor II
Regular Contributor II

@Dhruv_Sharma , 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!

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

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