asked on

Blank results from one column based on status of another column

I have a table that feeds a website and I want to only display the email address that don't have the NOTDSPLY flag set to -1. I do want the rest of the data to display, basically I want to blank out the email address where NOTDSPLY = -1 I tried CASE WHEN NOTDSPLY =-1 THEN EMAIL = '' ELSE EMAIL END AS EMAIL but that didn't work

SELECT        TOP (100) PERCENT STOCKING_DEALER_NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, PHONE_NO, LOWER(EMAIL) AS EMAIL, LOWER(ISNULL(MONDAY, 'NA')) 
                         AS MONDAY, LOWER(ISNULL(TUESDAY, 'NA')) AS TUESDAY, LOWER(ISNULL(WEDNESDAY, 'NA')) AS WEDNESDAY, LOWER(ISNULL(THURSDAY, 'NA')) 
                         AS THURSDAY, LOWER(ISNULL(FRIDAY, 'NA')) AS FRIDAY, LOWER(ISNULL(SATURDAY, 'NA')) AS SATURDAY, LOWER(ISNULL(SUNDAY, 'NA')) AS SUNDAY, 
                         CREDATE, MODDATE, INACTIVE
FROM            dbo.ATI_STDLR

Open in new window

ASKER CERTIFIED SOLUTION

dsacker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

You're close. EMAIL='' would attempt to set the value, and all you're trying to do is conditionally display it, so...

CASE WHEN NOTDSPLAY = -1 THEN '' ELSE EMAIL END as EMAIL

btw if it helps I have an article called SQL Server CASE Solutions that's a good read.

Fred Webb

ASKER

Thanks to both of you but dsacker got you by 4 tenths of a sec Jim. Thanks for pointing to your article it was a good read.