Checking for null in Xtab query

I have the following query and it works. Where the left part of the join doe
s
not have any records i'd like to return a zero. I tried the case but, ...
thanks all
kes
select
c.calId,
c.calDate,
c.calShortDesc,
c.calDesc,
c.CalHoliday,
e3.evtAll,
CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
e3.SS,
e3.WS,
e3.C,
e3.WE,
e3.ED,
e3.NW
from cal c
LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
(select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdatetime
= e.evtdatetime) as NS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdatetime
= e.evtdatetime) as SS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdatetime
= e.evtdatetime) as WS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdatetime
= e.evtdatetime) as C,
(select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdatetime
= e.evtdatetime) as WE,
(select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdatetime
= e.evtdatetime) as ED,
(select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdatetime
= e.evtdatetime) as NW
from evt e
group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
where c.calDate >= '20050626' and c.calDate <= '20050806'
(I can post the Data def, but this is a simple question a bout returning a
zero for null)will this work?
CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
or is there a better idea?
thanks
kes
"WebBuilder451" wrote:

> I have the following query and it works. Where the left part of the join d
oes
> not have any records i'd like to return a zero. I tried the case but, ...
> thanks all
> kes
> select
> c.calId,
> c.calDate,
> c.calShortDesc,
> c.calDesc,
> c.CalHoliday,
> e3.evtAll,
> CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
> e3.SS,
> e3.WS,
> e3.C,
> e3.WE,
> e3.ED,
> e3.NW
> from cal c
> LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdateti
me
> = e.evtdatetime) as NS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdateti
me
> = e.evtdatetime) as SS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdateti
me
> = e.evtdatetime) as WS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdateti
me
> = e.evtdatetime) as C,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdateti
me
> = e.evtdatetime) as WE,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdateti
me
> = e.evtdatetime) as ED,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdateti
me
> = e.evtdatetime) as NW
> from evt e
> group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
> where c.calDate >= '20050626' and c.calDate <= '20050806'
> (I can post the Data def, but this is a simple question a bout returning a
> zero for null)
>
>|||Yes, that will work.
Why haven't you tried it?
ML|||You don't even need a Case expression. The following will work:
IsNull(e3.NS,0) as NS
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
> will this work?
> CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
> or is there a better idea?
> thanks
> kes
> "WebBuilder451" wrote:
>|||that's the answer !!!
thanks
IOU1 kes
"JT" wrote:

> You don't even need a Case expression. The following will work:
> IsNull(e3.NS,0) as NS
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
>
>