CODEWITHSUNDEEP
sqlsql-serversql-server-2008if-statementconstraints
user1234
user1234

Reputation: 21

Applying a rule

SELECT LTRIM(RTRIM(HOST0149.LOGINID)) AS LOGINID,
       LTRIM(RTRIM(HOST0140.EMAIL)) AS EMAIL,
       LTRIM(RTRIM(HOST0149.USERKEY)) AS ROLE
  FROM HOST0149 LEFT JOIN 
       HOST0140 ON HOST0149.PERSONKEY = HOST0140.PERSONKEY

Hi, I am trying to apply a rule on the select for email column. Sometimes the login ID will be the users email address, how do I apply a rule that if the email appears in the Login ID, to return nothing for the email column? Any help would be much appreciated, thanks.

Upvotes: 1

Views: 57

Answers (3)

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

This will get result:

select LOGINID, EMAIL, ROLE FROM(SELECT LTRIM(RTRIM(HOST0149.LOGINID)) AS LOGINID,
       LTRIM(RTRIM(HOST0140.EMAIL)) AS EMAIL,
       LTRIM(RTRIM(HOST0149.USERKEY)) AS ROLE
  FROM HOST0149 LEFT JOIN 
       HOST0140 ON HOST0149.PERSONKEY = HOST0140.PERSONKEY)
WHERE EMAIL NOT LIKE  '%@%'

Upvotes: 1

Alex
Alex

Reputation: 23300

CASE...WHEN should do. Basically, you handle both cases explicitely.

SELECT LTRIM(RTRIM(HOST0149.LOGINID)) AS LOGINID,
   CASE WHEN LTRIM(RTRIM(HOST0140.EMAIL)) = LTRIM(RTRIM(HOST0149.LOGINID)) THEN NULL ELSE LTRIM(RTRIM(HOST0140.EMAIL)) END AS EMAIL,
   LTRIM(RTRIM(HOST0149.USERKEY)) AS ROLE
FROM HOST0149 LEFT JOIN 
   HOST0140 ON HOST0149.PERSONKEY = HOST0140.PERSONKEY

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I think a case expression does what you want:

SELECT LOGINID,
       (CASE WHEN LOGINID <> EMAIL THEN EMAIL END) as EMAIL,
       ROLE
FROM (SELECT LTRIM(RTRIM(HOST0149.LOGINID)) AS LOGINID,
             LTRIM(RTRIM(HOST0140.EMAIL)) AS EMAIL,
             LTRIM(RTRIM(HOST0149.USERKEY)) AS ROLE
      FROM HOST0149 LEFT JOIN 
           HOST0140
           ON HOST0149.PERSONKEY = HOST0140.PERSONKEY
     ) hh

Upvotes: 1

Related Questions