Email Generation for Multiple Domain

[sabthamis]

Hi  Everyone,

We are trying to generate the email address for 2 different domain I tried below code and it picks only the first case for all scenarios when cp28 is not 4160 also anything i m missing in below code.

CASE WHEN users.customproperty28='4160'
THEN
(
#CONCAT(
LOWER(REPLACE(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', ''), '.', '')),
'.',
LOWER(REPLACE(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', ''), '.', '')),
'.sb@testmy.com'
) #
CONCAT(
LOWER(REPLACE(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', ''), '.', '')),
'.',
LOWER(REPLACE(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', ''), '.', '')),
'1.sb@testmy.com'
) #
CONCAT(
LOWER(REPLACE(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', ''), '.', '')),
'.',
LOWER(REPLACE(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', ''), '.', '')),
'2.sb@testmy.com'
) #
)
ELSE

#CONCAT(
LOWER(REPLACE(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', ''), '.', '')),
'.',
LOWER(REPLACE(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', ''), '.', '')),
'.sb@testyour.com'
) #
CONCAT(
LOWER(REPLACE(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', ''), '.', '')),
'.',
LOWER(REPLACE(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', ''), '.', '')),
'1.sb@testyour.com'
) #
CONCAT(
LOWER(REPLACE(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', ''), '.', '')),
'.',
LOWER(REPLACE(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', ''), '.', '')),
'2.sb@testyour.com'
) #
END#

Thanks.

[rushikeshvartak]

CASE 
    WHEN users.customproperty28 = '4160' THEN
        CASE 
            WHEN some_other_condition = 1 THEN CONCAT(
                LOWER(REPLACE(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', ''), '.', '')),
                '.',
                LOWER(REPLACE(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', ''), '.', '')),
                '.sb@testmy.com'
            )
            WHEN some_other_condition = 2 THEN CONCAT(
                LOWER(REPLACE(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', ''), '.', '')),
                '.',
                LOWER(REPLACE(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', ''), '.', '')),
                '1.sb@testmy.com'
            )
            ELSE CONCAT(
                LOWER(REPLACE(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', ''), '.', '')),
                '.',
                LOWER(REPLACE(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', ''), '.', '')),
                '2.sb@testmy.com'
            )
        END
    ELSE
        CASE 
            WHEN condition1= 1 THEN CONCAT(
                LOWER(REPLACE(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', ''), '.', '')),
                '.',
                LOWER(REPLACE(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', ''), '.', '')),
                '.sb@testyour.com'
            )
            WHEN condition2= 2 THEN CONCAT(
                LOWER(REPLACE(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', ''), '.', '')),
                '.',
                LOWER(REPLACE(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', ''), '.', '')),
                '1.sb@testyour.com'
            )
            ELSE CONCAT(
                LOWER(REPLACE(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', ''), '.', '')),
                '.',
                LOWER(REPLACE(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', ''), '.', '')),
                '2.sb@testyour.com'
            )
        END
END

[sabthamis]

Hi Rushikesh,

Thanks for your response on this . What does the below condition means.

some_other_condition = 1

condition2= 2

[sabthamis]

@rushikeshvartak I tried the below code logics but dint work

case when ((users.companyname='xyzcomapany')
THEN
concat(upper(substring(users.firstname, 1, 1)),lower(substring(users.firstname, 2)),'.',upper(substring(users.lastname, 1, 1)), lower(substring(users.lastname, 2)),'@xyzcomapany.com')#
concat(upper(substring(users.firstname, 1, 1)),lower(substring(users.firstname, 2)),'.',upper(substring(users.lastname, 1, 1)), lower(substring(users.lastname, 2)),'1','@xyzcomapany')#
concat(upper(substring(users.firstname, 1, 1)),lower(substring(users.firstname, 2)),'.',upper(substring(users.lastname, 1, 1)), lower(substring(users.lastname, 2)),'2','@xyzcomapany'))
ELSE
(concat(upper(substring(users.firstname, 1, 1)),lower(substring(users.firstname, 2)),'.',upper(substring(users.lastname, 1, 1)), lower(substring(users.lastname, 2)),'@abc')#
concat(upper(substring(users.firstname, 1, 1)),lower(substring(users.firstname, 2)),'.',upper(substring(users.lastname, 1, 1)), lower(substring(users.lastname, 2)),'1','@abc')#
concat(upper(substring(users.firstname, 1, 1)),lower(substring(users.firstname, 2)), '.',upper(substring(users.lastname, 1, 1)), lower(substring(users.lastname, 2)),'2','@abc')#
concat(upper(substring(users.firstname, 1, 1)),lower(substring(users.firstname, 2)),'.',upper(substring(users.lastname, 1, 1)), lower(substring(users.lastname, 2)),'3','@abc'))END#

[rushikeshvartak]

You can add your logic if any or remove