Error while using multiple datasets in preprocessor/modifyuserdatajson

[IGAQ]

Hello All,

We have HR source integration - we are planning to transform  the data using mulitple datasets, please find below 

============================

"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,SYSTEMUSERNAME,EMAIL,USERNAME,CUSTOMPROPERTY25 FROM USERS ",
"DATASET_VALUES": "SELECT ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,DATASETNAME FROM DATASET_VALUES WHERE DATASETNAME ='DEFAULT_DEPARTMENTS'",
"DATASET_VALUES1":"SELECT ATTRIBUTE1,ATTRIBUTE3 FROM DATASET_VALUES WHERE DATASETNAME = 'DEFAULT_LOCATIONS'"
},

"TABLEINDEXES": {
"CURRENTUSERS": [
"USERNAME"
],
"NEWUSERDATA": [
"USERNAME"
],
"CURRENTDATASET_VALUES": [
"ATTRIBUTE1",
"ATTRIBUTE2",
"ATTRIBUTE3"
],
"CURRENTDATASET_VALUES1": [
"ATTRIBUTE1",
"ATTRIBUTE3"
]
}

User import is not successful and Its throwing below error in logs

java.sql.SQLSyntaxErrorException: Table 'ssminlp.tempdataset_values_838481' doesn't exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMappi

please let me know any inputs

Thanks,

sb

[rushikeshvartak]

if you remove dataset1 is it working ?

[IGAQ]

Yes @rushikeshvartak  It works fine if we use only one dataset.

[naveenss]

Hi @IGAQ please see the below JSON. This is working for me. Validate your JSON and change the preprocessor query as per yours. Let me know if you have any questions!

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT USERKEY,SYSTEMUSERNAME,EMAIL,USERNAME,CUSTOMPROPERTY25 FROM USERS ",
    "DATASET_VALUES": "SELECT ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,DATASETNAME FROM DATASET_VALUES WHERE DATASETNAME ='DEFAULT_DEPARTMENTS'",
    "DATASET_VALUES1": "SELECT ATTRIBUTE1,ATTRIBUTE3 FROM DATASET_VALUES WHERE DATASETNAME = 'DEFAULT_LOCATIONS'"
  },
  "COMPUTEDCOLUMNS": [
    "CUSTOMPROPERTY1"
  ],
  "TABLEINDEXES": {
    "CURRENTUSERS": [
      "USERNAME"
    ],
    "NEWUSERDATA": [
      "USERNAME"
    ],
    "CURRENTDATASET_VALUES": [
      "ATTRIBUTE1",
      "ATTRIBUTE2",
      "ATTRIBUTE3"
    ],
    "CURRENTDATASET_VALUES1": [
      "ATTRIBUTE1",
      "ATTRIBUTE3"
    ]
  },
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY1=(select CURRENTDATASET_VALUES.ATTRIBUTE1 from CURRENTDATASET_VALUES where CURRENTDATASET_VALUES.ATTRIBUTE2='Code2') "
  ]
}

[IGAQ]

Hello @naveenss  , I am using below query it gives the error mentioned above

UPDATE NEWUSERDATA NU SET NU.DEPARTMENTNAME=(CASE WHEN NU.TITLE IN ('XYZ','PQR') THEN (SELECT ATTRIBUTE3 FROM CURRENTDATASET_VALUES1 WHERE ATTRIBUT3='M50') END)

thanks,Sb

[rushikeshvartak]

Share full error in logs file