Creation Of Email via Email Generation Rule

[varunsharma]

Hi All,

I am trying to create Users with different email domain based on location. Below is the code.

The ask is if user location is IND then email domain should be created as username@XYZ.com whereas if country is MEX,US,UK,AUS etc then email should be created as username@abc.com.

Below is the code for reference, can anyone help me in rectifying this code as it generating error.

case when user.location like 'IND-%' replace(concat((users.firstname), ".", (users.lastname), "@XYZ.com"),' ','')
else case when country in('MEX','GTM','SLV','COL','CRI','ARG','CHL','PAN') then replace(concat((users.firstname), ".", substring_index(lastname,' ',1), "@abc.com"),' ','')
when country in ('BRA') then replace(concat((users.firstname), ".", substring_index(lastname,' ',-1), "@abc.com"),' ','')
else replace(concat((users.firstname), ".", (users.lastname), "@abc.com"),' ','') end end
#
(case when user.location like 'IND%' replace(concat((users.preferedfirstname), ".", (SUBSTRING(users.firstname, 1, 1)), ".", (users.lastname), "@XYZ.com"),' ','')else case when users.middlename is not null then case when country in('MEX','GTM','SLV','COL','CRI','ARG','CHL','PAN') then replace(concat((users.preferedfirstname), ".", substring_index(lastname,' ',1), "@abc.com"),' ','')
when country in ('BRA') then replace(concat((users.preferedfirstname), ".", substring_index(lastname,' ',-1), "@abc.com"),' ','')
else replace(concat((users.preferedfirstname), ".", (users.middlename), ".", (users.lastname), "@abc.com"),' ','') end else case when country in('MEX','GTM','SLV','COL','CRI','ARG','CHL','PAN') then replace(concat((users.preferedfirstname), ".",substring_index(lastname,' ',1), "@abc.com"),' ','')
when country in ('BRA') then replace(concat((users.preferedfirstname), ".", substring_index(lastname,' ',-1), "@abc.com"),' ','')
else replace(concat((users.preferedfirstname), ".", (SUBSTRING(users.firstname, 1, 1)), ".", (users.lastname), "@abc.com"),' ','') end end end)

[Darshanjain]

Hi @varunsharma 

What is the error you are seeing, it looks like first condition with user.location is wrong , it should be users.location.

CASE
WHEN users.location LIKE 'IND-%' THEN REPLACE(CONCAT(users.firstname, ".", users.lastname, "@XYZ.com"), ' ', '')
ELSE
CASE
WHEN country IN ('MEX', 'GTM', 'SLV', 'COL', 'CRI', 'ARG', 'CHL', 'PAN') THEN REPLACE(CONCAT(users.firstname, ".", SUBSTRING_INDEX(users.lastname, ' ', 1), "@abc.com"), ' ', '')
WHEN country = 'BRA' THEN REPLACE(CONCAT(users.firstname, ".", SUBSTRING_INDEX(users.lastname, ' ', -1), "@abc.com"), ' ', '')
ELSE REPLACE(CONCAT(users.firstname, ".", users.lastname, "@abc.com"), ' ', '')
END
END#
(CASE
WHEN users.location LIKE 'IND%' THEN REPLACE(CONCAT(users.preferedfirstname, ".", SUBSTRING(users.firstname, 1, 1), ".", users.lastname, "@XYZ.com"), ' ', '')
ELSE
CASE
WHEN users.middlename IS NOT NULL THEN
CASE
WHEN country IN ('MEX', 'GTM', 'SLV', 'COL', 'CRI', 'ARG', 'CHL', 'PAN') THEN REPLACE(CONCAT(users.preferedfirstname, ".", SUBSTRING_INDEX(users.lastname, ' ', 1), "@abc.com"), ' ', '')
WHEN country = 'BRA' THEN REPLACE(CONCAT(users.preferedfirstname, ".", SUBSTRING_INDEX(users.lastname, ' ', -1), "@abc.com"), ' ', '')
ELSE REPLACE(CONCAT(users.preferedfirstname, ".", users.middlename, ".", users.lastname, "@abc.com"), ' ', '')
END
ELSE
CASE
WHEN country IN ('MEX', 'GTM', 'SLV', 'COL', 'CRI', 'ARG', 'CHL', 'PAN') THEN REPLACE(CONCAT(users.preferedfirstname, ".", SUBSTRING_INDEX(users.lastname, ' ', 1), "@abc.com"), ' ', '')
WHEN country = 'BRA' THEN REPLACE(CONCAT(users.preferedfirstname, ".", SUBSTRING_INDEX(users.lastname, ' ', -1), "@abc.com"), ' ', '')
ELSE REPLACE(CONCAT(users.preferedfirstname, ".", SUBSTRING(users.firstname, 1, 1), ".", users.lastname, "@abc.com"), ' ', '')
END
END
END)