IS NULL in a Case Statement

Question

IS NULL in a Case Statement

ssmith 29602

SSC-Addicted

Points: 422
More actions
May 10, 2013 at 9:34 am

#394754

This is probably very simple but I can't get it to work.
If trying to write this
Case Oppo_stage When 'Bus_Written' Then (oppo_closed is null or Oppo_Closed >= GETDATE() -7)
but I'm being told there is incorrect syntax near the keyword 'is'
Can anyone help?
many thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

ssmith 29602 (5/10/2013)
This is probably very simple but I can't get it to work.
If trying to write this
Case Oppo_stage When 'Bus_Written' Then (oppo_closed is null or Oppo_Closed >= GETDATE() -7)
but I'm being told there is incorrect syntax near the keyword 'is'
Can anyone help?
many thanks.

Yep bad syntax. Unfortunately with what you posted there really isn't much to go to help. Perhaps if you posted the entire query we might be able to figure out what you are trying to accomplish.

ssmith 29602 SSC-Addicted Points: 422 More actions · Answer 2

Sorry, here the the complete statement

Select

oppo_opportunityref

,oppo_stage

,oppo_closed

From

Opportunity

Where

Case Oppo_stage When 'Bus_Written' Then (oppo_closed is null or Oppo_Closed >= GETDATE() -7)

Basically I'm trying to find all the Bus_Written opportunities where the closed date field is either null of within the last 7 days.

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

ssmith 29602 (5/10/2013)
This is probably very simple but I can't get it to work.
If trying to write this
Case Oppo_stage When 'Bus_Written' Then (oppo_closed is null or Oppo_Closed >= GETDATE() -7)
but I'm being told there is incorrect syntax near the keyword 'is'
Can anyone help?
many thanks.

Total Stab in the dark 😉

CASE WHEN Oppo_stage = 'Bus_Written' AND (oppo_closed IS NULL OR Oppo_Closed > = GETDATE() -7) THEN Do Something END

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

ssmith 29602 (5/10/2013)
Sorry, here the the complete statement
Select
oppo_opportunityref
,oppo_stage
,oppo_closed
From
Opportunity
Where
Case Oppo_stage When 'Bus_Written' Then (oppo_closed is null or Oppo_Closed >= GETDATE() -7)
Basically I'm trying to find all the Bus_Written opportunities where the closed date field is either null of within the last 7 days.

Give this a shot:

Select

oppo_opportunityref

,oppo_stage

,oppo_closed

From

Opportunity

Where

Oppo_stage = 'Bus_Written'

AND (oppo_closed is null or Oppo_Closed >= GETDATE() -7)

ssmith 29602 SSC-Addicted Points: 422 More actions · Answer 5

One day I'll learn to give you all the information at the first go.

There are more different stages then just Bus_Written and I need to include them too. That's why I was trying to put it in a Case statement.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

ssmith 29602 (5/10/2013)
One day I'll learn to give you all the information at the first go.
There are more different stages then just Bus_Written and I need to include them too. That's why I was trying to put it in a Case statement.

Full details, you may not need to use case.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

If you also want to return records where Oppo_stage != 'Bus_Written' (wthout any other checks for date (closed)) then try this:

Select

oppo_opportunityref

,oppo_stage

,oppo_closed

From

Opportunity

Where Oppo_stage != 'Bus_Written'

OR (

Oppo_stage = 'Bus_Written'

AND (oppo_closed is null or Oppo_Closed >= GETDATE() -7)

)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Case is not a control flow statement, it can't return portions of the query. It returns expressions (values)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

GilaMonster (5/10/2013)
Case is not a control flow statement, it can't return portions of the query. It returns expressions (values)

Precisely! Thank you for calling attention to it. It was my thought to post a similar message as I was reading the thread. The terminology should be the teacher here.

@ssmith 29602:

Directly from Books Online article CASE (Transact-SQL) - SQL Server 2008 R2 (bold and italics added):

CASE (Transact-SQL)
Evaluates a list of conditions and returns one of multiple possible result expressions.
The CASE expression has two formats:
- The simple CASE expression compares an expression to a set of simple expressions to determine the result.
- The searched CASE expression evaluates a set of Boolean expressions to determine the result.

You can compare the result of a CASE expression to a column or variable or the result of another expression (e.g. result of a call to a function) but you cannot use a CASE expression to dictate the control flow, i.e. to directly influence the code that is executed.

There is a control flow construct in many classical programming languages called a switch statement (a.k.a. switch/case statement) which uses "case" as a keyword and that is where I think most of the confusion comes from, however there is no such construct in T-SQL.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato