SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IS NULL in a Case Statement


IS NULL in a Case Statement

Author
Message
ssmith 29602
ssmith 29602
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 166
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91369 Visits: 38950
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ssmith 29602
ssmith 29602
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 166
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
Andy Hyslop
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2085 Visits: 3045
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
Lynn Pettis
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91369 Visits: 38950
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)




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ssmith 29602
ssmith 29602
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 166
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
Lynn Pettis
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91369 Visits: 38950
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11886 Visits: 5478
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215326 Visits: 46270
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
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37756 Visits: 14411
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search