Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
04/10/2024 08:53 AM - edited 04/10/2024 08:56 AM
Hello All,
I want to set CP20 to UUID of users which are getting access to specific application, when I am putting query like below, it keeps Updating the CP20 though its one time activity I want to do for each user, of course new user it may need to set CP20 or may not depending on CP31 value.
Can you please help me set CP20=UUID when CP31 is set to FISIBS, so that it does not keep updating every time, I tried following two but no luck !
UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid SET NEWUSERDATA.CUSTOMPROPERTY20 = CASE WHEN (CURRENTUSERS.CUSTOMPROPERTY31 = 'FIS IBS' AND (CURRENTUSERS.CUSTOMPROPERTY20 is NULL OR CURRENTUSERS.CUSTOMPROPERTY20='')) THEN UUID() END
UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid SET NEWUSERDATA.CUSTOMPROPERTY20=UUID() where (CURRENTUSERS.CUSTOMPROPERTY31 = 'FIS IBS' AND (CURRENTUSERS.CUSTOMPROPERTY20 is NULL OR CURRENTUSERS.CUSTOMPROPERTY20=''))
Even I tried below query(like just set for all user) which sets in one import and makes blank in next import
UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid SET NEWUSERDATA.CUSTOMPROPERTY20 =CASE WHEN (CURRENTUSERS.CUSTOMPROPERTY20 is null) THEN UUID() END
Thanks
Mahesh
Solved! Go to Solution.
04/10/2024 07:35 PM
UPDATE NEWUSERDATA
LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid
SET NEWUSERDATA.CUSTOMPROPERTY20 = CASE
WHEN CURRENTUSERS.CUSTOMPROPERTY31 = 'FIS IBS'
AND (NEWUSERDATA.CUSTOMPROPERTY20 IS NULL OR NEWUSERDATA.CUSTOMPROPERTY20 = '')
THEN UUID()
ELSE NEWUSERDATA.CUSTOMPROPERTY20
END;
04/11/2024 12:15 PM - edited 04/11/2024 12:49 PM
Appreciate your inputs here
Apologies, earlier I sent it too early. The Updates keep same value Updating as below
Thanks
Mahesh
04/11/2024 07:55 PM
UPDATE NEWUSERDATA
LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid
SET NEWUSERDATA.CUSTOMPROPERTY20 = CASE
WHEN CURRENTUSERS.CUSTOMPROPERTY31 = 'FIS IBS'
AND (NEWUSERDATA.CUSTOMPROPERTY20 IS NULL OR NEWUSERDATA.CUSTOMPROPERTY20 = '')
THEN UUID()
ELSE NEWUSERDATA.CUSTOMPROPERTY20
END WHERE CURRENTUSERS.CUSTOMPROPERTY20 IS NULL
04/14/2024 01:57 PM - edited 04/14/2024 01:58 PM
It did not work, Thanks very much for providing inputs . After doing some trials ,following I used and it is now working.
UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid SET NEWUSERDATA.CUSTOMPROPERTY20 = CASE WHEN (CURRENTUSERS.CUSTOMPROPERTY31 = 'FIS IBS' AND (CURRENTUSERS.CUSTOMPROPERTY20 is NULL OR CURRENTUSERS.CUSTOMPROPERTY20='')) THEN UUID() ELSE CURRENTUSERS.CUSTOMPROPERTY20 END
Thanks
Mahesh