• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

SQL Help

I'm having a bit of a problem with the CASE statement in my code below. Any ideas why it's not working.

Basically I have SSRS report that has a drop down of all 50 states. The report returns all the Contacts and Firms in the chosen State. I was asked to also add Northern California to the list of states.

So my goal is to say that if it's a normal state, then simply return all contacts in that State. If user chose Northern California, return contacts only in zip codes 93000 - 96000.
Select C.FirstName as 'FirstName', C.LastName as 'LastName', F.FirmName as 'Firm', C.Email
From Contacts C JOIN Firms F ON C.FirmNo = F.FirmNo
JOIN State S ON S.StateNo = F.StateNo
Where C.NoMarketing = 0
AND C.Email != ''
AND CASE @State WHEN @State like 'California - Northern Cali' THEN F.PostCode BETWEEN 93000 AND 96000
		ELSE S.StateName = @State
END


Select S.StateName as 'StateName'
From State S
Where S.[Disabled] = 0
AND CountryNo = 295
AND StateNo NOT IN (159,162,160,161)
UNION
Select 'California - Northern Cali'
From State S
Where S.[Disabled]=0

Open in new window

0
InfoTechEE
Asked:
InfoTechEE
  • 2
1 Solution
 
lluddenCommented:
Select C.FirstName as 'FirstName', C.LastName as 'LastName', F.FirmName as 'Firm', C.Email
From Contacts C JOIN Firms F ON C.FirmNo = F.FirmNo
JOIN State S ON S.StateNo = F.StateNo
Where C.NoMarketing = 0
AND C.Email != ''
AND ((@State = 'California - Northern Cali' AND F.PostCode BETWEEN 93000 AND 96000) OR
            (S.StateName = @State))

0
 
InfoTechEEAuthor Commented:
Getting a weird error on exectution...

An error occurred during local report processing.
Cannot read the next data row for the dataset RB8.
Conversion failed when converting the varchar value '91521-4583' to data type int.

So I just changed the above code to

AND ((@State = 'California - Northern Cali' AND F.PostCode BETWEEN '93000' AND '96000') OR
            (S.StateName = @State))

Is that OK?
0
 
lluddenCommented:
I forgot the quotes, so good catch.  That should do it.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now