How to add Else to the select from where SQL-CodePudding

I want to add to this code an ELSE profil_code='abc_all'

SELECT abc.contact_id
FROM   t_sup_supplier s 
INNER JOIN t_bas_address adr ON adr.adr_id=s.adr_id_office
OUTER APPLY (
    SELECT l.contact_id
    FROM t_usr_login l
    INNER JOIN t_usr_login_profil LP ON lp.login_name=l.login_name
    WHERE profil_code=CONCAT('abc_', adr.country_code)
) abc
WHERE s.sup_id=@x_id

How can I do it?

So if the last part profil_code=CONCAT('abc_', adr.country_code) was not found, it should take profil_code='abc_all'

CodePudding user response：

WHERE profil_code= COALESCE( CONCAT('abc_', adr.country_code), 'abc_all')

CodePudding user response：

You can use a COALESCE function in the WHERE clause:

WHERE profil_code = COALESCE(
    CONCAT('abc_', adr.country_code), 
    'abc_all'
)

Or use a OR operator:

WHERE 
    (profil_code=CONCAT('abc_', adr.country_code) or profil_code = 'abc_all')

CodePudding user response：

This will do:

profil_code=CONCAT('abc_', adr.country_code) or profil_code='abc_all'