select 'BILL TO' as C.CMP_NAME
      ,'ORDER#' as H.ord_hdrnumber
      ,'MOVEMENT' as H.mov_number
      ,'INVOICE#' as CONVERT(VARCHAR(12),H.ivh_invoicenumber)
      ,'TOTAL' as SUM(d.ivd_charge)
  from invoiceheader H
       INNER JOIN invoicedetail D
               ON H.ivh_hdrnumber = D.ivh_hdrnumber
       INNER JOIN COMPANY C
               ON H.IVH_BILLTO = C.CMP_ID
 WHERE H.mov_number NOT IN (select DISTINCT evt_mov_number
                              from event
                             where evt_trailer2 <> 'UNKNOWN'
                               and evt_enddate >= '20110101'
                           )
    or H.mov_number is null

quote:

---

Originally posted by ITTrucker

My fault for simplifying the query too much. There are a bunch of where clauses already in the query and I want to evaluate those as well. I don't want to keep all the NULL values, just the ones that fit the rest of the where clauses.

But I think I figured it out. I have other subselects that are not eliminating the NULL values, so instead of evaluating on the H.mov_number which has NULL values, I added an additional join in my subselect so I was evaluating on the H.ord_hdrnumber which is 0 for null values, but returns the data I'm looking for.


WHERE
H.ord_hdrnumber NOT in (select DISTINCT o.ord_hdrnumber from event e inner join orderheader o on e.evt_mov_number = o.mov_number where evt_trailer2 <> 'UNKNOWN' and evt_enddate >= '20110101')


Thanks for your help.

---