subquery null problem 2

[mitsiguri]

Hi,

wonder if someone can help me

when siteid is null in the subquery, the count returned is 0. is there any way to return a value when the siteid is null. i tried setting ansi_nulls off but it doesnt seem to work in this subquery. heres the sql:


set ansi_nulls off
insert into [stats](StatDate,SiteID,SiteName,StatType,UniqueHits,TotalHits,JoinHits)
select *, JoinHits = (select count(*) from referer where refdirection='OUT' and RefType=#TempItems.StatType and refdate=#TempItems.StatDate and siteid=#TempItems.SiteID)
from #TempItems order by statdate,stattype
set ansi_nulls on


TIA
alan

 

[grahamm]

How about trying:-

insert into [stats](StatDate,SiteID,SiteName,StatType,UniqueHits,TotalHits,JoinHits)
select *, JoinHits = (select count(*) from referer where refdirection='OUT' and RefType=#TempItems.StatType and refdate=#TempItems.StatDate
and (siteid=#TempItems.SiteID or siteid is NULL)
from #TempItems order by statdate,stattype

 

[mitsiguri]

that gives me a count when siteid is null on the sub query, but when it isnt null, it includes both the siteid and null in the count.

i dont understand why set ansi_nulls off doesnt make it work

this works:

set ansi_nulls off
select count(*) from referer where refdirection='OUT' and RefType='FREELMC' and refdate='11/15/2001' and siteid = NULL
set ansi_nulls on

but this doesnt?:

set ansi_nulls off
insert into [stats](StatDate,SiteID,SiteName,StatType,UniqueHits,TotalHits,JoinHits)
select *, JoinHits = (select count(*) from referer where refdirection='OUT' and RefType=#TempItems.StatType and refdate=#TempItems.StatDate and siteid=#TempItems.SiteID)
from #TempItems order by statdate,stattype
set ansi_nulls on



TIA
alan

 

[grahamm]

I'm not sure I understand your expectation:-

With set ansii_nulls off are you expecting:

siteid=#TempItems.SiteID

to evaluate to true if siteid is null and #TempItems.SiteID is not null?

 

[mitsiguri]

i want to evaluate both situations at once

#TempItems contains siteids both null and not null.

with ansi_nulls on it should allow me to get a value for JoinHits using siteid=#TempItems.SiteID (where sometimes siteid is an int or its a null)

but it doesnt work and i dont know why

 

[grahamm]

Ok agreed..

while column = NULL or column = @v (where @v is NULL) evals to TRUE columnA = columnB evals to FALSE when both are null. See also

http://support.microsoft.com/support/kb/articles/Q214/6/01.ASP

The workaround would be
insert into [stats](StatDate,SiteID,SiteName,StatType,UniqueHits,TotalHits,JoinHits)
select *, JoinHits = (select count(*) from referer where refdirection='OUT' and RefType=#TempItems.StatType and refdate=#TempItems.StatDate
and
(siteid=#TempItems.SiteID
or (siteid is NULL and #TempItems.SiteID is NULL))
)

from #TempItems order by statdate,stattype

 

[mitsiguri]

Excellent dude, it worked, you are an sql god!

cheers
alan

 

[grahamm]

I'm glad to help...