[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Case Statement

Posted on 2011-04-19
2
Medium Priority
?
329 Views
Last Modified: 2012-05-11
I have query using a CASE statment and I am getting a conversion error.

The issue is in CASE [c], the value is 0 so it should return 'n/a' , but for some reason it also tries to check when value is 1 in which case the select statement would return NULL since it does not exist and is giving me a conversion error.  "Conversion failed when converting datetime from character string"

How can I prevent the query from running the CASE [c]  = 1 select statement if CASE [c]  = 0  ?
select 
Ccustno, 
JobNumber, 
convert(varchar(10),DueDate,101) as DueDate, 
MailClass,
CASE [a] 
when 0 then 'n/a'
when 1 then (Select MAX([StopTime]) from [JobStepsStartStopTimes] where [Processid] = 0 and Jobnumber = 620854) 
END
as [PUT ON COMPUTER],
CASE [b] 
when 0 then 'n/a'
when 1 then (Select MAX([StopTime]) from [JobStepsStartStopTimes] where [Processid] = 1 and Jobnumber = 620854) 
END
as [CONVERT TO DBF]
,
CASE [c] 
when 0 then 'n/a'
when 1 then (Select MAX([StopTime]) from [JobStepsStartStopTimes] where [Processid] = 2 and Jobnumber = 620854) 
END
as [PREPARE SAMPLE FILES]
from dbo.sales_jobentry
where jobnumber = 620854

Open in new window

0
Comment
Question by:handyjay
2 Comments
 
LVL 6

Accepted Solution

by:
hyphenpipe earned 2000 total points
ID: 35425981
The two data types, character and datetime are not compatible.

Change your line in the case statement to:

when 1 then (Select convert(varchar, MAX([StopTime])) from [JobStepsStartStopTimes] where [Processid] = 2 and Jobnumber = 620854)
0
 

Author Closing Comment

by:handyjay
ID: 35426110
Thanks this works
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question