Re: Help needed with IIF syntax

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Ray Higdon (sqlhigdon_at_nospam.yahoo.com)
Date: 02/23/04 

Date: Sun, 22 Feb 2004 23:37:20 -0500

I was unclear on what you were doing there. The case statement makes
anything that is null return a 0, if you do not want to see nulls then take
the case statement out and use this in your where clause

        where checknum is not null 

-- 
Ray Higdon MCSE, MCDBA, CCNA
---
"Suzanne" <flaminstar@aol.com> wrote in message
news:14ef201c3f9c2$3f4453c0$a601280a@phx.gbl...
> Hi..That seems to have eliminated the errors for the
> first lines.  How would you change the 'Having'
> statement..with the extra check for 'is not null?
>
> HAVING ((IIf(Not IsNull([checknum]),[CheckNum],0)) Is Not
> Null
>      And (IIf(Not IsNull([checknum]),[CheckNum],0))<>"0")
>
> Thank you!
> >-----Original Message-----
> >Use CASE:
> >
> >select case when checknum is null then 0 else checknum
> end AS
> >CheckNumber
> >....
> >
> >group by case when checknum is null then 0 else checknum
> end
> >
> >HTH
> >
> >-- 
> >Ray Higdon MCSE, MCDBA, CCNA
> >---
> >"Suzanne" <flaminstar@aol.com> wrote in message
> >news:140a001c3f9ba$e5e61400$a101280a@phx.gbl...
> >> Hi...I'm converting this database to SQL Server 2000.
> I
> >> have this query that is checking if a field contains a
> >> value or not..if not, then set the value to zero.
> >> However, Query Analyzer is giving me the error "syntax
> >> error near the keyword Not'" on the first line (the
> >> Select statement).  I've tried various changes...I
> would
> >> appreciate a second set of eyes to help.
> >>
> >> Here's the query ...
> >>
> >> SELECT IIf(Not IsNull([checknum]),[CheckNum],0) AS
> >> CheckNumber, tblApCheckHist.CheckDate AS Expr1
> >>
> >> FROM tblApCheckHist
> >>
> >> GROUP BY IIf(Not IsNull([checknum]),[CheckNum],0),
> >> tblApCheckHist.CheckDate
> >>
> >> HAVING ((IIf(Not IsNull([checknum]),[CheckNum],0)) Is
> Not
> >> Null
> >>      And (IIf(Not IsNull([checknum]),[CheckNum],0))
> <>"0")
> >>
> >> ORDER BY IIf(Not IsNull([checknum]),[CheckNum],0),
> >> tblApCheckHist.CheckDate;
> >>
> >> Any suggestions are appreciated!!
> >
> >
> >.
> >

Relevant Pages

  • Re: Help needed with IIF syntax
    ... >select case when checknum is null then 0 else checknum ... >> appreciate a second set of eyes to help. ... >> Here's the query ... ...
    (microsoft.public.sqlserver.programming)
  • Re: Help needed with IIF syntax
    ... select case when checknum is null then 0 else checknum end AS ... Query Analyzer is giving me the error "syntax ... > error near the keyword Not'" on the first line (the ... > appreciate a second set of eyes to help. ...
    (microsoft.public.sqlserver.programming)
  • Re: Help needed with IIF syntax - Thank you!!
    ... >select case when checknum is null then 0 else checknum ... >> appreciate a second set of eyes to help. ... >> Here's the query ... ... >> CheckNumber, tblApCheckHist.CheckDate AS Expr1 ...
    (microsoft.public.sqlserver.programming)