?
Solved

Conditional where statement to include or exclude null values

Posted on 2009-04-30
4
Medium Priority
?
850 Views
Last Modified: 2012-05-06
I am trying to create a conditional where statement in a t-sql query to include or exclude a set of records based on whether a certain field is null or not. When I try to run the query I get the following error: Incorrect syntax near the keyword 'IS'. The query is quite long so I've included just the where statement.
Is it possible to build a conditional statement based on Null values and if so could someone please help me correct my mistake.
Thanks in advance.
WHERE     Case when (MOnth(Now) = 4) then (InvoicedAmountTBL_2.[Posting Date] IS NOT NULL OR
                      InvoicedAmountTBL_2.[Posting Date] IS NULL) and (dbo.[Gallowglass Live$Job Task].[Booking Status] = 0 OR
                      dbo.[Gallowglass Live$Job Task].[Booking Status] = 1 OR
                      dbo.[Gallowglass Live$Job Task].[Booking Status] = 2) else (InvoicedAmountTBL_2.[Posting Date] IS NOT NULL) and (dbo.[Gallowglass Live$Job Task].[Booking Status] = 0 OR
                      dbo.[Gallowglass Live$Job Task].[Booking Status] = 1 OR
                      dbo.[Gallowglass Live$Job Task].[Booking Status] = 2) end

Open in new window

0
Comment
Question by:Fester7572
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
ezraa earned 1600 total points
ID: 24270918
Your use of case is not correct here.  You should be performing the logic in the Where clause like so:
WHERE   ((MOnth(Now) = 4) and
         ((InvoicedAmountTBL_2.[Posting Date] IS NOT NULL OR
                      InvoicedAmountTBL_2.[Posting Date] IS NULL) and (dbo.[Gallowglass Live$Job Task].[Booking Status] = 0 OR
                      dbo.[Gallowglass Live$Job Task].[Booking Status] = 1 OR
                      dbo.[Gallowglass Live$Job Task].[Booking Status] = 2)))
	OR ((MOnth(Now) <> 4) and
		 ((InvoicedAmountTBL_2.[Posting Date] IS NOT NULL) and (dbo.[Gallowglass Live$Job Task].[Booking Status] = 0 OR
                      dbo.[Gallowglass Live$Job Task].[Booking Status] = 1 OR
                      dbo.[Gallowglass Live$Job Task].[Booking Status] = 2)))

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24270932
> to include or exclude a set of records based on whether a certain field is null or not

Is this to include the columns in the SELECT  list ? in that case you should use a dynamic query or some if condition
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 24270947
ezraa,

NOW  -> should be changed to  GETDATE()

Aneesh
0
 

Author Closing Comment

by:Fester7572
ID: 31576471
Thanks for your very prompt assistance guys. That sorted it. Guess I need to do some research about dynamic sql and how to use case properly.
Thanks again.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

621 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