Email Generation rule by advance query

[Rohit_Mishra]

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

[rushikeshvartak]

Refer sample

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

[Rohit_Mishra]

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

[rushikeshvartak]

Please share draft and mention issue you are currently facing.

[Rohit_Mishra]

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

[rushikeshvartak · Accepted solution]

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

[Rohit_Mishra]

Hi @rushikeshvartak 

You're a life saver 🙂 

Thank you so much

Rohit A Mishra