checking for Null value

Hi,
I'm drawing a blank on how to do this, I'm trying to return the rows
where TempERCP.PI_Therapy column does not contain 'sphincterotomy' but,
I also want the rows that contain 'Null' there which isn't being returned?
SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate, TempERCP.Hospno
, TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
FROM
TempERCP INNER JOIN
TempDemo ON TempERCP.Hospno = TempDemo.Hospno
where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
( TempERCP.PI_Therapy not in ('sphincterotomy') )
order by TempERCP.Examdate, TempERCP.Hospno
thanks
gvdu... I think this is it
( TempERCP.PI_Therapy not in ('sphincterotomy')or TempERCP.PI_Therapy is
null )
would I use this COALESCE? example of using this
which is better?
thanks
gv
"gv" <viatorg@.musc.edu> wrote in message
news:exTZ7J2$FHA.3872@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm drawing a blank on how to do this, I'm trying to return the rows
> where TempERCP.PI_Therapy column does not contain 'sphincterotomy' but,
> I also want the rows that contain 'Null' there which isn't being returned?
>
> SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate,
> TempERCP.Hospno
> , TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
> FROM
> TempERCP INNER JOIN
> TempDemo ON TempERCP.Hospno = TempDemo.Hospno
> where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
> ( TempERCP.PI_Therapy not in ('sphincterotomy') )
> order by TempERCP.Examdate, TempERCP.Hospno
> thanks
> gv
>
>|||gv wrote:
> Hi,
> I'm drawing a blank on how to do this, I'm trying to return the rows
> where TempERCP.PI_Therapy column does not contain 'sphincterotomy'
> but,
> I also want the rows that contain 'Null' there which isn't being
> returned?
>
> SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate,
> TempERCP.Hospno , TempERCP.Full_Name,TempERCP.PI_Therapy,
> TempERCP.comment FROM
> TempERCP INNER JOIN
> TempDemo ON TempERCP.Hospno = TempDemo.Hospno
> where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
> ( TempERCP.PI_Therapy not in ('sphincterotomy') )
> order by TempERCP.Examdate, TempERCP.Hospno
>
You need to set ansi_nulls off:
select 'test' pi_therapy into #test
union all
select null
union all
select 'sphincterotomy'
set ansi_nulls on
select pi_therapy from #test where
pi_therapy not in ('sphincterotomy')
set ansi_nulls off
select pi_therapy from #test where
pi_therapy not in ('sphincterotomy')
drop table #test
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate, TempERCP.Hospno,
TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
FROM
TempERCP INNER JOIN
TempDemo ON TempERCP.Hospno = TempDemo.Hospno
where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
( TempERCP.PI_Therapy not in ('sphincterotomy') )
Union
SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate, TempERCP.Hospno,
TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
FROM
TempERCP INNER JOIN
TempDemo ON TempERCP.Hospno = TempDemo.Hospno
where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
( TempERCP.PI_Therapy is Null )
order by TempERCP.Examdate, TempERCP.Hospno
Personally I see the word Sphincterotomy and I just want to run as far and
fast as I can!
"gv" <viatorg@.musc.edu> wrote in message
news:exTZ7J2$FHA.3872@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm drawing a blank on how to do this, I'm trying to return the rows
> where TempERCP.PI_Therapy column does not contain 'sphincterotomy' but,
> I also want the rows that contain 'Null' there which isn't being returned?
>
> SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate,
> TempERCP.Hospno
> , TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
> FROM
> TempERCP INNER JOIN
> TempDemo ON TempERCP.Hospno = TempDemo.Hospno
> where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
> ( TempERCP.PI_Therapy not in ('sphincterotomy') )
> order by TempERCP.Examdate, TempERCP.Hospno
> thanks
> gv
>
>|||thanks guys
gv
"gv" <viatorg@.musc.edu> wrote in message
news:exTZ7J2$FHA.3872@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm drawing a blank on how to do this, I'm trying to return the rows
> where TempERCP.PI_Therapy column does not contain 'sphincterotomy' but,
> I also want the rows that contain 'Null' there which isn't being returned?
>
> SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate,
> TempERCP.Hospno
> , TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
> FROM
> TempERCP INNER JOIN
> TempDemo ON TempERCP.Hospno = TempDemo.Hospno
> where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
> ( TempERCP.PI_Therapy not in ('sphincterotomy') )
> order by TempERCP.Examdate, TempERCP.Hospno
> thanks
> gv
>
>