• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

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?

  • 2
1 Solution
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?

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now