July 9, 2012 at 5:13 am
hi
i'my building an sql string in access through some list box selections. the end result is a where clause that looks like...
where company IN (1, 2, 3, NULL)
and area IN (a, b, c, NULL)
problem is that the NULLs are not being returned. If i use IS NULL i get an error.
is this possible the way i'm trying to do it??
Thanks
July 9, 2012 at 5:34 am
Hi
have you tried
WHERE
(CompanyID IN (1,2,3) OR CompanyID IS NULL)
AND (AreaID IN (1,2,3) OR AreaID IS NULL)
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 9, 2012 at 5:42 am
Sounds like a catch all query
Please read through this
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
July 9, 2012 at 8:51 am
The reason for this is that IN is effectively a series of OR statements checking for equality. Nothing equals NULL, so that data will never be returned. The 2nd post's solution is viable.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2012 at 10:10 am
It looks like you are capturing all values of a list box (selected and unselected). Please pass only selected values from the list. It should be fine with simple IN query.
Else split the predicate in two parts: one with IN clause and another with IS NULL.
July 10, 2012 at 1:44 am
thanks for the replies, my scenario is....
i have a list of sales which are allocated to a division (home, export) and area (county for home, continent for export). in some cases an area is not allocated so i must have a NULL value in my list box to bring back those lines.
it means i don't think this catch all query 'ProductID = @product Or @product IS NULL' will work becuase i don't alway want to return NULLs, only if selected.
i build the string in VBA which looks creates this...
select * from table
where division = @HOME
and area in (@AREA)
if i change it to 'and area in (@AREA) or area is null' i'll have data returned i don't want?
is this the only way?
Thanks
July 10, 2012 at 3:08 am
The alternative is, to pass an additional parameter when Null is selected, based on the flag you can call different queries. Yes, you need to write two different queries. Either it can be in the Same Stored procedure or in two different stored procedure.
If @IsNullSelected then
Begin
Select
....
From
....
WHERE (Area in (1,3,4,5) or Area is NULL)
End
Else
Begin
Select
....
From
....
WHERE Area in (1,3,4,5)
End
Hope, this will help.
Note that having IF... Else condition is not an good idea. I will prefer to have two SP to be called based on the selection.
Thanks,
JM
July 10, 2012 at 6:09 am
Assuming the data supports null being the equivalent of 0 and company and area being integer fields, then the following should work.
where isnull(company, 0) IN (1, 2, 3, 0)
and isnull(area,0) IN (a, b, c, 0)
July 10, 2012 at 6:26 am
thanks kenny
that worked a treat.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply