Click here to Skip to main content
15,867,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm having trouble understanding how to use the Case Statement in a Select query. What I'm trying to do is change the data from 1s and 0s to Yes and No. IsLaptop is a field within my customers table that has 1 and 0 not allowing nulls. Below is my whole Select Query, the Case keeps throwing "Syntax error (missing operator) in query expression 'CASE IsLaptop WHEN "1" THEN "Yes" WHEN "0" THEN "No" END'.

SQL
SELECT Customers.CustomerID, Customers.OldCustomerID, LastName+','+FirstName+' '+MiddleName AS [Customer Name], Left(FirstName,1)+Left(MiddleName,1)+LastName AS UserName, zlu_Cars.Description AS Car, zlu_CarColor.Description AS [Car Color], zlu_Computers.Description AS Computer, 


CASE IsLaptop WHEN "1" THEN "Yes" WHEN "0" THEN "No" END AS Laptop,


zlu_Race.Description AS Race, zlu_Residence.Description AS Residence, zlu_BirthMonth.Description AS [Birth Month]
 
FROM (((((Customers INNER JOIN zlu_Cars ON Customers.CarID = zlu_Cars.CarID) INNER JOIN zlu_CarColor ON Customers.CarColorID = zlu_CarColor.CarColorID) INNER JOIN zlu_Computers ON Customers.ComputerID = zlu_Computers.ComputerID) INNER JOIN zlu_Race ON Customers.RaceID = zlu_Race.RaceID) INNER JOIN zlu_Residence ON Customers.ResidenceID = zlu_Residence.ResidenceID) INNER JOIN zlu_BirthMonth ON Customers.BirthMonthID = zlu_BirthMonth.BirthMonthID
WHERE (((Customers.CustomerID)>499 And (Customers.CustomerID)<601))
ORDER BY Customers.LastName, Customers.FirstName;
Posted

1 solution

The case statement seems to be correct, check the other sections of the code..
SQL
SELECT 
	Customers.CustomerID, 
	Customers.OldCustomerID, 
	LastName+','+FirstName+' '+MiddleName AS [Customer Name], 
	Left(FirstName,1)+Left(MiddleName,1)+LastName AS UserName, 
	zlu_Cars.Description AS Car, 
	zlu_CarColor.Description AS [Car Color], 
	zlu_Computers.Description AS Computer, 
 	CASE IsLaptop WHEN '1' THEN 'Yes' WHEN '0' THEN 'No' END AS Laptop,
 	zlu_Race.Description AS Race, 
 	zlu_Residence.Description AS Residence, 
 	zlu_BirthMonth.Description AS [Birth Month]
FROM (((((Customers INNER JOIN zlu_Cars ON Customers.CarID = zlu_Cars.CarID) INNER JOIN zlu_CarColor ON Customers.CarColorID = zlu_CarColor.CarColorID) INNER JOIN zlu_Computers ON Customers.ComputerID = zlu_Computers.ComputerID) INNER JOIN zlu_Race ON Customers.RaceID = zlu_Race.RaceID) INNER JOIN zlu_Residence ON Customers.ResidenceID = zlu_Residence.ResidenceID) INNER JOIN zlu_BirthMonth ON Customers.BirthMonthID = zlu_BirthMonth.BirthMonthID
WHERE (((Customers.CustomerID)>499 And (Customers.CustomerID)<601))
ORDER BY Customers.LastName, Customers.FirstName;

--Example..

SELECT Name, Rating,
CASE Rating
WHEN 'V' THEN 'Very Good'
WHEN 'X' THEN 'Poor'
ELSE 'Average'
END AS myratings 
FROM table_name;
 
Share this answer
 
Comments
Anthony Bond 15-Jan-12 13:06pm    
Silly mistake I'm was trying to run the CASE Statement in MS Access however access cannot read that case statement I transferred it over to my SQL Server and ran the query no errors and was happy the I got the correct output. Thank you for the double check. Check my other question on CONCAT_WS trying to create a username in SQL however it returns 0 instead of the wanted data.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900