SQL Statement does not work on where clause.

Posted on 2005-04-29
Last Modified: 2013-12-18
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?

Question by:cyle
    LVL 9

    Accepted 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?

    Author Comment

    I cannot get 1061 in the analyzer? But if I run without filtering I get all data?
    LVL 14

    Expert Comment

    THat means that the record is not there. check in the analyzer by giving

    select payee from d100.appay;

    LVL 9

    Expert Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
    For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now