syntax error - in know these records are there

I know data values exist for this string below.  Am I doing something wrong in the syntax

select * from tblJJDP where Fiscal = 2012 and not IVPA = 'I'
al4629740Asked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>many values in IVPA have either an 'I' or NULL
You'll have to handle the NULL values then, something like using COALESCE, which returns the first non-null value between the parentheses:

select *
from tblJJDP
where Fiscal = 2012 and COALESCE(IVPA, 'foo') <>  'I'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Possibly, but without access to your data source it's hard to tell.

Knee-jerk reactions...

>Fiscal = 2012
this works if Fiscal is some kind of Numeric.  If it's a char then you'll need Fiscal = '2012'

>and not IVPA = 'I'
and IVPA <> 'I' would be a little cleaner
verify that you want AND instead of OR
Verify that there is capital I, and not also lowercase i records that you want in your results
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Fiscal = 2012
Also if Fiscal is a date then Year(Fiscal) = 2012
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
NalinkumarbalajiCommented:
Try this one

select * from tblJJDP where Fiscal ='2012' and IVPA <> 'I'

Go with jimhorn comment
0
 
al4629740Author Commented:
still nothing.

I do notice that many values in IVPA have either an 'I' or NULL
0
 
awking00Commented:
Another method -
select * from tblJJdp where Fiscal = 2012
except
select * from tblJJdp where IVPA = 'I'
0
 
al4629740Author Commented:
Thats crazy I never knew that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.