Re: How to query with multiple criteria on single field

D wrote:
> Hello -
> I'm attempting to pull a query (below)...my question is...if a
> single column in my query contains NULL values and I want to include
> those results, how can I "double" query to include the null values?
> In my WHERE clause I have IDST_HOUSE_SERVICE_AVLBLTY.SVC_AVL_TYPE NOT
> IN ('2','3','4','5') but this does not return the null svc_type
> values...why?
>
>
>
> SELECT Distinct IDST_HOUSE.ACCTCORP, IDST_HOUSE.HOUSE,
> IDST_HOUSE.CUST, IDST_HOUSE.COMPLEX, IDST_HOUSE.DWELL,
> IDST_HOUSE.INFO, IDST_HOUSE.MKT, IDST_HOUSE.CAREA,
> IDST_AUXILIARY_HOUSE.Y1, IDST_HOUSE_VOIP_DATA.RATECENTER,
> IDST_HOUSE.FIBERNODE, IDST_CUSTOMER.WRT,
> IDST_HOUSE_SERVICE_AVLBLTY.SVC_AVL_TYPE,
> IDST_HOUSE_SERVICE_AVLBLTY.SVC_STATUS
> FROM IDST_HOUSE left outer join IDST_CUSTOMER
> ON IDST_HOUSE.ACCTCORP=IDST_CUSTOMER.ACCTCORP AND
> IDST_HOUSE.HOUSE=IDST_CUSTOMER.HOUSE left outer join
> IDST_HOUSE_VOIP_DATA
> ON IDST_HOUSE.ACCTCORP=IDST_HOUSE_VOIP_DATA.ACCTCORP AND
> IDST_HOUSE.HOUSE=IDST_HOUSE_VOIP_DATA.HOUSE left outer join
> IDST_AUXILIARY_HOUSE
> ON IDST_HOUSE.ACCTCORP=IDST_AUXILIARY_HOUSE.ACCTCORP AND
> IDST_HOUSE.HOUSE=IDST_AUXILIARY_HOUSE.HOUSE left outer join
> IDST_HOUSE_SERVICE_AVLBLTY
> ON IDST_HOUSE.ACCTCORP=IDST_HOUSE_SERVICE_AVLBLTY.ACCTCORP AND
> IDST_HOUSE.HOUSE=IDST_HOUSE_SERVICE_AVLBLTY.HOUSE
> WHERE IDST_HOUSE.ACCTCORP='9518' AND
> IDST_HOUSE_SERVICE_AVLBLTY.SVC_AVL_TYPE NOT IN ('2','3','4','5) AND
> IDST_HOUSE_SERVICE_AVLBLTY.SVC_STATUS<>'1' AND
> IDST_HOUSE_VOIP_DATA.RATECENTER IS NULL
>

Because NULL is not a 2 a 3 a 4 or a 5.

Consider instead:

AND (<column> NOT IN (mylist) OR (<column> IS NULL))

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org