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.ACC TCORP 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

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.ACC TCORP 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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thanks, I tried this and didn't receive any results either - should've
posted that before. Perhaps the values are not NULL but instead have a
space with no other text. Hmmm...thanks again for the suggestion/
responding so quickly.