asked on

SQL Case Statement Question

Hi,

I have the following query that I am trying to modify to give me the correct results. Please see below

SELECT b.building_id, b.Display_Name, CASE WHEN b.City = 'Suburb - New York' THEN 'New York'
ELSE 'New York' END building_city,
b.[State]
FROM building b
WHERE b.City = 'New York'
AND b.[State] = 'NY'
ORDER by b.Display_Name

The problem is is only returns buildings that have an actual value of 'New York' for the city which is what I want, but I also want to say if the city has a value of 'Suburb - New York', then it should appear as well

I know "Suburb - New York" is not a city but I'm stuck working with the data this way so any help appreciated how I can modify my query.

-ws

Anthony Perkins

Something like this perhaps:

SELECT b.building_id,
b.Display_Name,
CASE WHEN b.City = 'Suburb - New York' THEN 'New York'
ELSE 'New York'
END building_city,
b.[State]
FROM building b
WHERE b.City IN ('New York', 'Suburb - New York')
AND b.[State] = 'NY'
ORDER BY b.Display_Name

Anthony Perkins

By the way the CASE statement in this case is somewhat meaningless: The result is always 'New York'

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

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

Start Free Trial

Westside2004

ASKER

My query is dynamic, I should've mentioned that. Your code works acperkins but the city and state are values I pass into this query so in the where clause, those two values are dynamic. As of now I am passing in a city of "New York", and a state of "NY"

Let me know if that makes sense...

-ws

Anthony Perkins

>>Your code works acperkins but the city and state are values I pass into this query so in the where clause, those two values are dynamic. <<
That is my point: Either City should not be included in the WHERE clause or the CASE statement is meaningless.

Westside2004

ASKER

Got it, I was able to use what you said....

Anthony Perkins

I do not believe that the Dynamic SQL has any bearing on it, but if you feel it does, feel free to post the relevant code.