Click HERE to see how Saviynt Intelligence is transforming the industry. |
04/12/2023 02:09 AM
Hi
I am using advance config for generating email for multiple domains and i have 1 use case where we can have multiple users with same name. therefore i referred below link to proceed.
Still i am not able to go to finish line, please suggest how can i proceed. i have pasted the query which i am using as below.
CASE WHEN (users.entity = 'ro') THEN concat (users.firstname , substring('.',1,1) , users.lastname , '@ro.com') end #
CASE WHEN (users.entity = 'HL') THEN concat (users.firstname , substring('.',1,1) , users.lastname , '@hi.com') end # CASE WHEN (users.username is users.firstname,".",users.lastname) THEN concat(users.firstname , substring('.',1,1) , users.lastname , ((RAND() * (68-10+1))+10), '@hi.com')
CASE WHEN (users.entity = 'dm') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@dm.net') end #
CASE WHEN (users.entity = 'co') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@co.com') end #
CASE WHEN (users.entity = 'ml') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@mi.com') end #
CASE WHEN (users.entity = 'mo') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@mo.com') end #
END
if possible, please rephrase my code so that it would be easier for me to enahnce in same direction.
Solved! Go to Solution.
04/12/2023 02:21 AM
It seems like your query is using CASE WHEN statements to generate email addresses based on the user’s entity. However, there seems to be an issue with the third CASE WHEN statement where you are trying to concatenate the user’s first name, last name and a random number between 10 and 68.
It looks like there is a syntax error in this statement. Instead of users.username is users.firstname,".",users.lastname, it should be users.username = concat(users.firstname,'.',users.lastname).
Here’s the updated query:
CASE WHEN (users.entity = 'ro') THEN concat (users.firstname , substring('.',1,1) , users.lastname , '@ro.com') end #
CASE WHEN (users.entity = 'HL') THEN concat (users.firstname , substring('.',1,1) , users.lastname , '@hi.com') end # CASE WHEN (users.username = concat(users.firstname,'.',users.lastname)) THEN concat(users.firstname , substring('.',1,1) , users.lastname , ((RAND() * (68-10+1))+10), '@hi.com')
CASE WHEN (users.entity = 'dm') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@dm.net') end #
CASE WHEN (users.entity = 'co') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@co.com') end #
CASE WHEN (users.entity = 'ml') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@mi.com') end #
CASE WHEN (users.entity = 'mo') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@mo.com') end #
END
Please validate and let us know if further details are needed on this.
04/12/2023 10:08 PM
Hi Dixshant,
I have tried above code but it's not working. I changed 1 attribute i.e. email in place of username. pasting the query again for reference.
CASE WHEN (users.entity = 'ro') THEN concat (users.firstname , substring('.',1,1) , users.lastname , '@ro.com') end #
CASE WHEN (users.entity = 'HL') THEN concat (users.firstname , substring('.',1,1) , users.lastname , '@hi.com') end # CASE WHEN (users.email = concat(users.firstname , substring('.',1,1) , users.lastname , '@hi.com') THEN concat(users.firstname , substring('.',1,1) , users.lastname , ((RAND() * (68-10+1))+10), '@hi.com')
CASE WHEN (users.entity = 'dm') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@dm.net') end #
CASE WHEN (users.entity = 'co') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@co.com') end #
CASE WHEN (users.entity = 'ml') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@mi.com') end #
CASE WHEN (users.entity = 'mo') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@mo.com') end #
END
What it does is it checks for username for reconciliation and it insert the identity but without email like blank email. As per logs, it ignores the rule if email exists.
Please advise how can i proceed now.
04/14/2023 08:50 AM
Hi Dixshant,
I am looking for your help here, please suggest me path for next step.
04/14/2023 08:54 AM
Hi @asharma,
Our team is currently investigating and we appreciate your patience while we work on this. We will provide you with an update as soon as possible regarding the status of the investigation and any potential solutions or next steps.
04/19/2023 08:06 AM
Hi Dixshant,
Any update on above?
Regards
04/21/2023 03:59 AM - edited 04/21/2023 04:00 AM
Based on your updated code, it seems like you are trying to generate email addresses for users based on their entity value, and for cases where the email address is already set to a specific value, you want to append a random number to the email address with the domain '@hi.com'. However, you mentioned that the email address is not being generated correctly and is showing as blank.
Here's a revised version of the code that may address the issue:
CASE
WHEN users.entity = 'ro' THEN concat(users.firstname, '.', users.lastname, '@ro.com')
WHEN users.entity = 'HL' THEN concat(users.firstname, '.', users.lastname, '@hi.com')
WHEN users.entity = 'dm' THEN concat(users.firstname, '.', users.lastname, '@dm.net')
WHEN users.entity = 'co' THEN concat(users.firstname, '.', users.lastname, '@co.com')
WHEN users.entity = 'ml' THEN concat(users.firstname, '.', users.lastname, '@mi.com')
WHEN users.entity = 'mo' THEN concat(users.firstname, '.', users.lastname, '@mo.com')
ELSE CASE
WHEN users.email = concat(users.firstname, '.', users.lastname, '@hi.com') THEN
concat(users.firstname, '.', users.lastname, ((RAND() * (68-10+1)) + 10), '@hi.com')
ELSE '' -- Default case for handling blank email
END
END
Please validate let me know if you have any further questions or issues.
04/21/2023 05:00 AM
Regards
04/26/2023 12:47 AM
Thank you for the update. We kindly request that you refer to the below query as an example and attempt to develop the code accordingly
For Reference:-
CASE
WHEN users.entity = 'ro' THEN CONCAT(users.firstname, '.', users.lastname, '@ro.com')
WHEN users.entity = 'HL' THEN CONCAT(users.firstname, '.', users.lastname, '@hi.com')
WHEN users.entity = 'dm' THEN CONCAT(users.firstname, '.', users.lastname, '@dm.net')
WHEN users.entity = 'co' THEN CONCAT(users.firstname, '.', users.lastname, '@co.com')
WHEN users.entity = 'ml' THEN CONCAT(users.firstname, '.', users.lastname, '@mi.com')
WHEN users.entity = 'mo' THEN CONCAT(users.firstname, '.', users.lastname, '@mo.com')
ELSE CONCAT(users.firstname, '.', users.lastname, IF(EXISTS(SELECT 1 FROM users u WHERE u.firstname = users.firstname AND u.lastname = users.lastname), '1', ''), '@hi.com')
END
04/27/2023 12:38 AM
It works this time, Thankyou Dixshant.
1 more query, what if we have to check with all entities for same condition.
04/27/2023 12:53 AM
Thank you for the update. We would appreciate it if you could mark this as a solution if the provided solution resolved your query. Additionally, for your second question, we kindly request that you try to create an example based on the given instructions, and please do not hesitate to let us know if you require any further assistance.