SQL Statement does not work on where clause.

I have this SQL query
         If (ocon.ConnectTo("AS400DB2", "", ""))  Then
         Set oqry.Connection = ocon
         SQLStringVar = "select docsrc,payee,dtetrn from D100.APPAY where payee=1061"  
         oqry.SQL = SQLStringVar
when I use a where clause I get no data BUT if I remove the where clause its okey. What's going on?

Who is Participating?
HappyFunBallConnect With a Mentor Commented:
do you have a payee that has a value of 1061?  Do the same query in Query Analyzer - does it return nothing there too?
cyleAuthor Commented:
I cannot get 1061 in the analyzer? But if I run without filtering I get all data?
THat means that the record is not there. check in the analyzer by giving

select payee from d100.appay;

I take it you don't have much experience with SQL statements.  Here's the overview on what the select statement is trying to do:

The keywords are SELECT, FROM and WHERE.  SELECT asks what columns you want shown in the rows that are returned.  FROM tells the server which data table to pull those rows from.  WHERE specifies some kind of filtering to limit the number of rows returned.  

If you eliminate the WHERE clause, you are saying "get me all the rows FROM the D100.APPAY table.  Show me only the docsrc, payee, and dtetrn columns"

If you add the WHERE clause it says:  "get me all the rows FROM the D100.APPAY table WHERE the payee column is equal to 1061.  Show me only the docsrc, payee, and dtetrn columns for that row"

If there's no row in the table that has a payee column equal to 1061 then you'll get no rows returned.

Make sense?
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.