Solved

Pervasive SQL 10 only give 200 records on a join querry

Posted on 2009-05-06
4
711 Views
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???
0
Comment
Question by:GeekHipster
  • 2
4 Comments
 
LVL 18

Assisted Solution

by:mirtheil
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.
0
 
LVL 28

Accepted Solution

by:
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 www.pervasive.com, and this should resolve it.  Check the README to verify that the issue (#57615) is resolved in there, if you wish.
0
 
LVL 1

Author Closing Comment

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

Long live MS SQL!!!
0
 
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...
0

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

Suggested Solutions

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
Creating and Managing Databases with phpMyAdmin in cPanel.
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…

743 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

12 Experts available now in Live!

Get 1:1 Help Now