[Webinar] Streamline your web hosting managementRegister Today


Pervasive SQL 10 only give 200 records on a join querry

Posted on 2009-05-06
Medium Priority
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
  • 2
LVL 18

Assisted Solution

mirtheil earned 200 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 29

Accepted Solution

Bill Bach earned 1800 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 www.pervasive.com, 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 29

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

591 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