Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
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 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 28

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

719 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