CODEWITHSUNDEEP
sqlif-statement
Deni Wag
Deni Wag

Reputation: 1

How to add Else to the select from where SQL

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'

Upvotes: 0

Views: 81

Answers (5)

Deni Wag
Deni Wag

Reputation: 1

So I tried as following:

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= COALESCE( CONCAT('abc_', adr.country_code), 'abc_all')
) abc
WHERE s.sup_id=@x_id

--> Here it is now showing the 'abc_all' but it is existing in the table

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) or profil_code = 'abc_all')
) abc
WHERE s.sup_id=@x_id

--> Here it is working but only partly. Because if both values are existing it is also showing me both. I need only the first value. If first not exist only then the second.

Upvotes: 0

Deni Wag
Deni Wag

Reputation: 1

the provided solutuions are working but not as I was expecting. now both are triggered. I need only if ('abc_', adr.country_code) was not found, then only take profil_code='abc_all'. But if the ('abc_', adr.country_code) was found then NOT take profil_code='abc_all

Upvotes: 0

Nikhil
Nikhil

Reputation: 3950

This will do:

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

Upvotes: 0

Fábio Lira
Fábio Lira

Reputation: 17

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')

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

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

Upvotes: 1

Related Questions