How to handle NULLs

• Isabelle2378

  SSCertifiable

  Points: 6174

  More actions

  August 4, 2006 at 11:33 am

  #68735

  Hi,

  I am slowly trying to get into the development side of the DBA world and I'm having a problem with a simple query.  I have a table that I need to return all rows that have a valid value in every column.  So, for example: vendor_code, vendor_name, vendor_address, vendor_city, vendor_state, vendor_zip.  I want to return all the vendors that have a value in these columns.  The problem is that some have NULL in vendor_address, or NULL in vendor_zip and so on.  So I keep getting rows with NULL values.  Here is what I tried:

  select vendor_code, vendor_name, vendor_addr_l1, vendor_addr_l2, vendor_addr_l3, vendor_addr_l4, vendor_addr_l5 from dbo.pocvnms

  where vendor_addr_l1 is not null

  and vendor_addr_l2 is not null

  and vendor_addr_l3 is not null

  and vendor_addr_l4 is not null

  and vendor_addr_l5 is not null

  and vendor_code like '10%'

  or vendor_code like '20%'

  Vendor_code         vendor_addr_l1           vendor_addr_l3 vendor_addr_l4

  200004 AERCHEM, INC.  1615 N. BARCLAY BLVD.   NULL   BUFFALO

  What am I doing wrong?

  Thanks!

  Isabelle 

  Thanks!
  Bea Isabelle

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  August 4, 2006 at 11:46 am

  #653402

  You need to group the OR clause with parenthesis:

  ...

  and ( vendor_code like '10%' or vendor_code like '20%' )

  Otherwise it sees all the possible AND statements, but because the vendor_code like '20%' satisfies the search condition it can discard the previous intended AND statements.

• Isabelle2378

  SSCertifiable

  Points: 6174

  More actions

  August 4, 2006 at 12:01 pm

  #653412

  Thank you Aaron!  That worked perfectly.

  Isabelle

  Thanks!
  Bea Isabelle

• Charles Kincaid

  SSChampion

  Points: 13593

  More actions

  August 7, 2006 at 11:08 am

  #653688

  Also look at COALESCE.  You can use this to substitute a value for NULLs.  Practical example is reading a customer credit limit from a legacy system.  In some cases the credit limit has not yet been defined and the column is NULL.  This causes my computations to thow chunks.  I fix this in the SELECT like this:

  COALESCE (CUST_CREDIT.LIMIT_ON_CREDIT,0) AS LIMIT_ON_CREDIT

  I get zero instead of NULL.  Check Books On-Line for more details.

  NOTE: COALESCE is a function and if you don't do the AS you will get "No Column Name".

   

  ATBCharles Kincaid