Click HERE to see how Saviynt Intelligence is transforming the industry. |
10/21/2024 04:11 AM
Hi,
I am knowing that the following advance query isn't in the right format to generate the email where the priority to preferredfirstname is given over the firstname.
I want to generate the email in the following fashion:
Ex. 1:
Ex. 2:
It would be great if anyone can let me know on how to do it in proper way.
10/21/2024 05:48 AM
try
(CASE
WHEN users.preferedfirstname IS NOT NULL AND TRIM(users.preferedfirstname) <> '' AND INSTR(users.preferedfirstname, ' ') > 0 THEN
REPLACE(CONCAT(
SUBSTRING_INDEX(users.preferedfirstname, ' ', 1), '_',
SUBSTRING_INDEX(users.preferedfirstname, ' ', -1), '_',
users.lastname,
IFNULL((SELECT DISTINCT(attribute2) FROM dataset_values WHERE attribute1 = users.customproperty30 AND datasetname = 'CONTRACTTYPE_SUFFIX_EMAIL'), ''),
'@xyz.com'
), ' ', '_')
ELSE
REPLACE(CONCAT(
users.preferedfirstname, '_',
users.lastname,
IFNULL((SELECT DISTINCT(attribute2) FROM dataset_values WHERE attribute1 = users.customproperty30 AND datasetname = 'CONTRACTTYPE_SUFFIX_EMAIL'), ''),
'@xyz.com'
), ' ', '_')
END)#
(CASE
WHEN users.firstname IS NOT NULL AND TRIM(users.firstname) <> '' AND INSTR(users.firstname, ' ') > 0 THEN
REPLACE(CONCAT(
SUBSTRING_INDEX(users.firstname, ' ', 1), '_',
SUBSTRING_INDEX(users.firstname, ' ', -1), '_',
users.lastname,
IFNULL((SELECT DISTINCT(attribute2) FROM dataset_values WHERE attribute1 = users.customproperty30 AND datasetname = 'CONTRACTTYPE_SUFFIX_EMAIL'), ''),
'@xyz.com'
), ' ', '_')
ELSE
REPLACE(CONCAT(
users.firstname, '_',
users.lastname,
IFNULL((SELECT DISTINCT(attribute2) FROM dataset_values WHERE attribute1 = users.customproperty30 AND datasetname = 'CONTRACTTYPE_SUFFIX_EMAIL'), ''),
'@xyz.com'
), ' ', '_')
END)
10/21/2024 06:33 AM
CASE
WHEN TRIM(users.preferedfirstname) IS NOT NULL
AND TRIM(users.preferedfirstname) <> ''
THEN
CASE
WHEN INSTR(users.preferedfirstname, ' ') > 0
THEN REPLACE(
CONCAT(
SUBSTRING_INDEX(users.preferedfirstname, ' ', 1), '_',
SUBSTRING_INDEX(users.preferedfirstname, ' ', -1), '_',
users.lastname,
IFNULL(
(SELECT DISTINCT(attribute2)
FROM dataset_values
WHERE attribute1 = users.customproperty30
AND datasetname = 'CONTRACTTYPE_SUFFIX_EMAIL'
), ''
), '@xyz.com'
), ' ', '_'
)
ELSE
REPLACE(
CONCAT(
users.preferedfirstname, '_',
users.lastname,
IFNULL(
(SELECT DISTINCT(attribute2)
FROM dataset_values
WHERE attribute1 = users.customproperty30
AND datasetname = 'CONTRACTTYPE_SUFFIX_EMAIL'
), ''
), '@xyz.com'
), ' ', '_'
)
END
ELSE
CASE
WHEN INSTR(users.firstname, ' ') > 0
THEN REPLACE(
CONCAT(
SUBSTRING_INDEX(users.firstname, ' ', 1), '_',
SUBSTRING_INDEX(users.firstname, ' ', -1), '_',
users.lastname,
IFNULL(
(SELECT DISTINCT(attribute2)
FROM dataset_values
WHERE attribute1 = users.customproperty30
AND datasetname = 'CONTRACTTYPE_SUFFIX_EMAIL'
), ''
), '@xyz.com'
), ' ', '_'
)
ELSE
REPLACE(
CONCAT(
users.firstname, '_',
users.lastname,
IFNULL(
(SELECT DISTINCT(attribute2)
FROM dataset_values
WHERE attribute1 = users.customproperty30
AND datasetname = 'CONTRACTTYPE_SUFFIX_EMAIL'
), ''
), '@xyz.com'
), ' ', '_'
)
END
END