Pervasive SQL 10 only give 200 records on a join querry

Posted on 2009-05-06
Last Modified: 2012-05-06
I am trying to get a shipping address list for all of my clients.
I have the following SQL.
SELECT C.CustID, C.CustName, A.AddrId, A.AddrName
FROM Addresses  AS A JOIN Customers AS C ON C.CustId = A.CustId

When I run the SQL by it self I get the first 200 records, but if I insert the result into tmpTable I get the whole list.

What gives???
Question by:GeekHipster
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 18

Assisted Solution

mirtheil earned 50 total points
ID: 24316542
What are you using to run the query?
Try in ODBC Test or PCC using  "Text" mode.  
I think some versions of the PCC only display the first 200 records initially when running the query in "Grid" mode.
LVL 28

Accepted Solution

Bill Bach earned 450 total points
ID: 24325879
The PCC had an error in certain cases.  Here's some more details about it:

APP - Queries Running with v10.10 PCC Returning 200 rows instead of entire data set
Application:      PCC      
Version:      PSQLv10.10.125      

Issue: (DT)
Win32 WGE V10.10.125, DEMODATA, PCC:
    SELECT * FROM  Person

Execute to grid,  everything is OK.
    SELECT * FROM Person
    ORDER BY First_name
Execute to grid,  you can not scroll down past row 200 !!!

But there is some kind of Candid Camera stuff here: if you enter COMMENT starting with "ORDER BY ", you can scroll down just fine. Enter this query (INCLUDING THE COMMENT!)
    SELECT * FROM Person
    -- ORDER BY blahblah
    ORDER BY First_Name
Execute to grid, you can scroll down  to the 1500th row.
 It does not matter what database name you use,  which field you "order by" on, or even to what server you connect (WGE 10.10 PCC querying V9.52 Server manifestates the same behaviour).

Resolution: (GS)
Goldstar Software tested as well with v10.10 server and 10.10.125 PCC release and duplicated the same item.  Here's some additional information:
- PCC "knows" the number of rows immediately.  Clicking on the first row will show either "1 of 1500" or "1 of 200" immediately -- you do not have to actually scroll down the list.
- It is NOT related to non-keyed queries, as I see the same results with:
    SELECT * FROM Person
    ORDER BY Last_Name
- The following query returns 200 records.  Note the angle brackets are simply there to show where spacing exists:
    <SELECT * FROM "Person" >
    <-- order by>
    <ORDER BY Last_Name>
    However, if you add a space at the end of the comment line, it returns all 1500 rows.
    <SELECT * FROM "Person" >
    <-- order by >
    <ORDER BY Last_Name>
 - In the above two queries, I ran with Query Plan Viewer enabled.  It logged THREE queries only.  The first query created a single entry for the SELECT statement.  The second query created TWO entries, one for the SELECT statement, and one for the following text in it:
    <select COUNT(*) from "Person">
    <-- >

Whatever the problem is, it seems systemic to the PCC and to the way it obtains record counts.  Interestingly, if I add "random" characters to the comment line (as in  "-- 12345 67 8910 "), the query still returns only 200 rows.  But, if I then add "-- ORDER BY " back in (even with the other characters after it), it returns 1500 again.  There must be some logic looking for the "ORDER BY" text in a comment???

Logged incident with Pervasive 177886 (JC).  JC reported that 10.12 should have it fixed, but this does NOT seem to be the case.

There is a CR created for the Grid returning only 200 records (57615).  It mentions how the counts are reversed and the queries to produce the issue.  

Try patching to the current version of PSQLv10 from, and this should resolve it.  Check the README to verify that the issue (#57615) is resolved in there, if you wish.

Author Closing Comment

ID: 31578513
The only thing I have to say is "Wow!! And they are still in business???"

Long live MS SQL!!!
LVL 28

Expert Comment

by:Bill Bach
ID: 24326992
>>Long live MS SQL!!!
Which is known as the only database engine and tool set without bugs available today...

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

740 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