Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IS NULL in a Case Statement Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 9:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:54 AM
Points: 18, Visits: 118
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.
Post #1451645
Posted Friday, May 10, 2013 9:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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.



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)
Post #1451651
Posted Friday, May 10, 2013 9:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:54 AM
Points: 18, Visits: 118
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.
Post #1451661
Posted Friday, May 10, 2013 9:52 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 689, Visits: 2,760
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
Post #1451662
Posted Friday, May 10, 2013 9:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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)





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)
Post #1451665
Posted Friday, May 10, 2013 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:54 AM
Points: 18, Visits: 118
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.
Post #1451669
Posted Friday, May 10, 2013 10:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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.



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)
Post #1451672
Posted Friday, May 10, 2013 10:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451675
Posted Friday, May 10, 2013 11:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1451698
Posted Friday, May 10, 2013 1:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1451730
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse