Email Generation Rule for preferredfirstname and Firstname

[anujapawar]

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.

(CASE

WHEN users.preferedfirstname IS NOT NULL AND trim(users.preferedfirstname <> ' ') AND INSTR(users.preferedfirstname,' ') > 0 THEN REPLACE(concat(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(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) #

I want to generate the email in the following fashion:

Ex. 1:

1. Preferredfirstname: John Mary Williams
2. Firstname: Joe Mary Williams
3. Lastname: Doe
4. Email: John_Williams_Doe_xyz.com

Ex. 2:

1. Firstname: Joe Mary Williams
2. Lastname: Doe
3. Email: Joe_Williams_Doe_xyz.com

It would be great if anyone can let me know on how to do it in proper way.

[Raghu]

@anujapawar 

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)

[rushikeshvartak]

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