Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Email Generation rule by advance query

Rohit_Mishra
New Contributor III
New Contributor III

Hi Experts,

Need a help in generating email using Advance query 

Logic  

use

firstname.lastname@domain.com

if duplicate exists like above, create firstname.middlename.lastname and if middlename is not there or duplicate exists for this also then firstname.lastame and auto-increment by 1 one on each duplicate 

Note: number appended should start from 2 and so on

I used the below code but it is not working
CASE
WHEN (users.firstname != '' AND users.firstname IS NOT NULL)
AND (users.lastname != '' AND users.lastname IS NOT NULL) THEN
(
SELECT
CASE
WHEN primary_email_count = 0 THEN
CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '@domain.com')
WHEN secondary_email_count = 0 THEN
CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '1@domain.com')
ELSE
CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), secondary_email_count + 1, '@domain.com')
END AS generated_email
FROM (
SELECT
COUNT(CASE WHEN u.email = CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '@domain.com') THEN 1 ELSE NULL END) AS primary_email_count,
COUNT(CASE WHEN u.email = CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '1@domain.com') THEN 1 ELSE NULL END) AS secondary_email_count
FROM users u
WHERE u.email LIKE CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '_', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '%@domain.com')
) AS email_check
)
END
Please help

Regards

Rohit A Mishra

6 REPLIES 6

rushikeshvartak
All-Star
All-Star

Refer sample

https://forums.saviynt.com/t5/identity-governance/email-generation-rule-by-advance-query/m-p/89057


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

Hi Rushi,

Thanks for quick response

I tried those sample snippets as well but still not able to achieve it, please if you could help in this

Regards

Rohit A Mishra

Please share draft and mention issue you are currently facing.


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

Rohit_Mishra
New Contributor III
New Contributor III

Hi Rushi

Please find the below darft
CASE
WHEN (users.firstname != '' AND users.firstname IS NOT NULL)
AND (users.lastname != '' AND users.lastname IS NOT NULL) THEN
(SELECT
CASE
WHEN COUNT(*) = 0 THEN
CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '.', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '@domain.com')
ELSE
CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '.', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), COUNT(*) + 1, '@domain.com')
END
FROM users u
WHERE u.email LIKE CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '.', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '%@domain.com'))
END
This is working for the 1st and 3rd cases, which are firstname.lastname@domain.com. In case of duplicate emails, it appends numbers. However, for the 2nd case, if a duplicate email is found, it should take the format firstname.middlename.lastname@domain.com. If this is also present, it should append a number in the format firstname.lastname2@domain.com.
Please check the above code as well which I mention initially in first story

Thank you so much for the help in advance 

Regards

Rohit A Mishra

CASE
WHEN (users.firstname != '' AND users.firstname IS NOT NULL)
AND (users.lastname != '' AND users.lastname IS NOT NULL) THEN
CASE
WHEN (
SELECT COUNT(*)
FROM users u
WHERE u.email = CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '.', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '@domain.com')
) = 0 THEN
CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '.', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '@domain.com')

WHEN (
SELECT COUNT(*)
FROM users u
WHERE u.email = CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '.', REPLACE(REPLACE(users.middlename, ' ', '_'), '''', ''), '.', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '@domain.com')
) = 0 AND users.middlename IS NOT NULL AND users.middlename != '' THEN
CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '.', REPLACE(REPLACE(users.middlename, ' ', '_'), '''', ''), '.', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '@domain.com')

ELSE
CONCAT(
REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '.', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''),
(
SELECT IFNULL(MAX(SUBSTRING_INDEX(SUBSTRING(u.email, LENGTH(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', '')) + LENGTH(REPLACE(REPLACE(users.lastname, ' ', '_'), '''', '')) + 3), '@', 1)), 1) + 1
FROM users u
WHERE u.email LIKE CONCAT(REPLACE(REPLACE(users.firstname, ' ', '_'), '''', ''), '.', REPLACE(REPLACE(users.lastname, ' ', '_'), '''', ''), '%@domain.com')
),
'@domain.com'
)
END
END AS email


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

Rohit_Mishra
New Contributor III
New Contributor III

Hi @rushikeshvartak 

You're a life saver 🙂 

Thank you so much

Rohit A Mishra