asked on

Conditional where statement to include or exclude null values

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

ASKER CERTIFIED SOLUTION

ezraa

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

> 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

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fester7572

ASKER

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.