Help with Case Syntax for MS SQL Server (or fixing my datatype error)

Databases
1

I am quite new to SQL scripting and have mostly worked with MySQL. I am now trying to create a script for use with MS SQL Server that will obtain a numeric value in a table and output an associated name (which is not in any table).

I haven’t worked with Case statements in SQL before, however I think my issue may have more to do with initializing a new variable I am introducing.

Here is my code:

select distinct r.FacilityId,  
case
	when r.FacilityId = 1 then 'GreatNorth'
	when r.FacilityId = 2 then 'Jackpot'
	when r.FacilityId = 3 then 'Cash'
	when r.FacilityId = 5 then 'Century'
	when r.FacilityId = 6 then 'Rivers'
	when r.FacilityId = 7 then 'Pure'
	else r.FacilityId
end as FacilityName
from Report_XV r

And here is the error I am getting:
Conversion failed when converting the varchar value ‘GreatNorth’ to data type int.

As an experiment I tired adding amending FacilityName to FacilityName(VARCHAR(40)) but that also returned an error. I also tried “cast(FacilityName as varchar(40))” but that also resulted in a syntax error – I clearly have no idea what I am doing.

Currently this is a test script, as I am trying to learn how to manipulate the data. Once I get this working, then I will be removing the reference to “r.FacilityId” as my first column of data.

2

I’m not very familiar with SQL Server, but what about something like this:

select distinct r.FacilityId,  FacilityName = 
case
	when r.FacilityId = 1 then 'GreatNorth'
	when r.FacilityId = 2 then 'Jackpot'
	when r.FacilityId = 3 then 'Cash'
	when r.FacilityId = 5 then 'Century'
	when r.FacilityId = 6 then 'Rivers'
	when r.FacilityId = 7 then 'Pure'
	else r.FacilityId
end
from Report_XV r

(ETA - Oh, maybe that’s Transact-SQL, I’m not familiar enough with it to know the difference).

EATA - Is the confusion because you sometimes return a string, or your ELSE clause returns whatever variable type your ID column contains? Perhaps if you modify your ELSE to return concat ('Not Found ', r.FacilityId) instead?

Is there a reason you can’t put these names into a separate table and just use a JOIN? It would save trouble when the names change, or you need to add another one.

1 Like
3

I would also strongly recommend to put the strings in an own table and just join it!

4

It’s the else. Because the FacilityId is a numeric, it’s defaulting to that type.

Change the else to convert the value to a string value.

else CONVERT(VARCHAR(20) r.FacilityId)

or ever better since the number is going to mean diddly to your end users

else 'Other'
5 
ELSE 'Other (' + r.FacilityId + ')'
1 Like
6

Even better since it would allow you to then fix the query to accommodate those missing values. Though if you have to make a lot changes, then adding the lookup table (which @Thallius suggested) would be the easier approach in the long run because you’d just have to add the appropriate row to the table and not have to mess with the query.

7

Thanks Thalius I like the idea of a table, but my inexperience with SQL, I am puzzled on how to do this. Do I do this with CREATE TABLE? I have googled around a bit, but I’m not sure I am using the right terms. If you know of a link to an example of this, could you please provide it.

8

It’s a standard create statement.

-- Create table
CREATE TABLE Facility (
  id INTEGER PRIMARY KEY,
  facilityName  VARCHAR(25) NOT NULL);

-- Put values into table...
INSERT INTO Facility (id, facilityName) VALUES (1, 'GreatNorth')
INSERT INTO Facility (id, facilityName) VALUES (2,'Jackpot')
INSERT INTO Facility (id, facilityName) VALUES (3,'Cash')
INSERT INTO Facility (id, facilityName) VALUES (5,'Century')
INSERT INTO Facility (id, facilityName) VALUES (6,'Rivers')
INSERT INTO Facility (id, facilityName) VALUES (7,'Pure')

Then your query becomes

SELECT DISTINCT 
       r.FacilityId
    ,  COALESCE(FacilityName, 'Unknown')
  FROM Report_XV r
 LEFT OUTER JOIN Facility f ON f.id = r.FacilityId

which would give you something like - Notice the unknown, hence the left outer join and the COALESCE

1|GreatNorth
2|Jackpot
6|Rivers
8|Unknown
1 Like
9

Thank you Dave.

I do have everything now working with the Case statement. I will now experiment with this option. I am not at all familiar with the COALESCE function, so I will read up on how that is used.

Thank you everyone for your help with this.

10

COALESCE returns the first NON NULL value from a list.

The reason I used it was to handle the “Other” situation from the case statement approach.

There are multiple ways to join tables The most common is an INNER JOIN which would only return the records that have a match on both tables.

So if I used an inner join, you would have gotten because there is no facility with id = 8 on the new facility table.

1|GreatNorth
2|Jackpot
6|Rivers

But if you want to see ALL facilities, you have to show those records that don’t have a match. That’s where a LEFT OUTER JOIN comes in. The left outer join return ALL of the rows from the first table, any matches from the joined table, and nulls where no match can be found. So without the coalesce, you’d get

1|GreatNorth
2|Jackpot
6|Rivers
8|NULL

But that’s not really user friendly, and in some cases, can be harmful if you don’t handle nulls in your code correctly. That’s where the COALESCE comes in.

COALESCE(FacilityName, 'Unknown')

This will return the FacilityName if a match was found. Otherwise, it will return the value ‘Unknown’

2 Likes
11

Thank you Dave, I really appreciate you taking the time to explain and detail all that out. I was also quite curious as to why a LEFT OUTER JOIN was needed, and that explained it very well. Thank you.

2 Likes
13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.