CODEWITHSUNDEEP
sql-servercase
user2630752
user2630752

Reputation: 1

Case Statement Error: an expression of non-boolean type specified in a context where condition is expected

I'm having trouble figuring out what the problem with this code is. Before I added the Case statement in the Where clause, the query was working fine, but when I added it in, I'm getting that error. If someone could please help me out, I would greatly appreciate it!!

Here is the case statement 

CASE 
    WHEN (GETDATE()< '2013-06-30 00:00:00.000' AND GETDATE() >= '2013-04-01 00:00:00.000') THEN 
        CASE WHEN (TargetStartDt >= '2013-04-01 00:00:00.000' AND TargetStartDt < '2013-06-30 00:00:00.000') THEN (JobStatus = 'Approved' OR JobStatus = 'Closed(Filled)')
        END 
    WHEN (GETDATE() < '2013-09-30 00:00:00.000' AND GETDATE() >= '2013-07-01 00:00:00.000') THEN 
        CASE WHEN TargetStartDt < '2013-06-30 00:00:00.000' THEN (JobStatus = 'Approved' OR JobStatus = 'Closed(Filled)' OR JobStatus = 'Closed(Not Filled)')
             WHEN (TargetStartDt >= '2013-07-01 00:00:00.000' AND TargetStartDt < '2013-09-30 00:00:00.000') THEN (JobStatus = 'Approved' OR JobStatus = 'Closed(Filled)')
        END  
    WHEN GETDATE() < '2013-12-31 00:00:00.000' AND GETDATE()>= '2013-10-01 00:00:00.000' THEN
        CASE WHEN TargetStartDt < '2013-09-30 00:00:00.000' THEN (JobStatus = 'Approved' OR JobStatus = 'Closed(Filled)' OR JobStatus = 'Closed(Not Filled)')
             WHEN (TargetStartDt >= '2013-10-01 00:00:00.000' AND TargetStartDt < '2013-12-31 00:00:00.000')  THEN (JobStatus = 'Approved' OR JobStatus = 'Closed(Filled)')
        END 
    --ELSE (JobStatus = 'Approved' OR JobStatus = 'Closed(Filled)' OR JobStatus = 'Closed(Not Filled)')
END

Thank you so much!!

Upvotes: 0

Views: 8559

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280423

CASE is an expression that returns a single value. It can't be used for boolean or control-of-flow logic. Therefore you can't say:

THEN (JobStatus = 'Approved' OR JobStatus = 'Closed(Filled)'
   OR JobStatus = 'Closed(Not Filled)')

You'll need to structure this clause differently. I'm not going to try to reverse engineer your entire query but perhaps instead of nested CASE it should be a single one:

CASE WHEN (date range checks) AND 
  (JobStatus IN ('Approved', 'Closed(Filled)', 'Closed(Not Filled)')) 
THEN ...

Upvotes: 2

Craig
Craig

Reputation: 3307

This looks like your problem:

THEN (JobStatus = 'Approved' OR JobStatus = 'Closed(Filled)' OR JobStatus = 'Closed(Not Filled)')

This type of clause occurs in a couple places. I'm not sure what you're trying to do, but the boolean expression shouldn't follow THEN

Upvotes: 0

Related Questions