Filter out SELECT if column value is NULL or Blank

chrisryhal
chrisryhal used Ask the Experts™
on
This query works good, but the problem I have is that sometimes there is a NULL and/or blank value for the "BinNum"

Can someone help me with this SELECT and make it to where it will filter out the ones where "binnum" is either NULL or ''

SELECT OnHandQty, Allocatedqty, binnum, lotnum, LotNum + ' / ' + BinNum + ' / ' AS BinDetails, (OnHandQty - AllocatedQty) AS UnAllocatedQty from partbin WHERE PartNum = '@PartNum' and BinNum Not In ('SHPG')

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
SELECT OnHandQty, Allocatedqty, binnum, lotnum, LotNum + ' / ' + BinNum + ' / ' AS BinDetails, (OnHandQty - AllocatedQty) AS UnAllocatedQty from partbin WHERE PartNum = '@PartNum' and BinNum Not In ('SHPG')
and BinNum <> '' and BinNum is not null

-Ajitha
Nathan RileyFounder
Commented:
just another quick change, might want to add the last one in, incase your blanks are spaces
SELECT OnHandQty,
Allocatedqty,
binnum,
lotnum,
LotNum + ' / ' + BinNum + ' / ' AS BinDetails,
(OnHandQty - AllocatedQty) AS UnAllocatedQty 
from partbin 
WHERE PartNum = '@PartNum' 
and BinNum Not In ('SHPG')
and BinNum not in ('',' ')
and BinNum is not null

Open in new window

AneeshDatabase Consultant
Top Expert 2009
Commented:
or like this

BinNum Not In ('SHPG')
and ISNULL(BinNum,'')  <> ''

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial