Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 813
  • Last Modified:

Pervasive SQL 10 only give 200 records on a join querry

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
GeekHipster
Asked:
GeekHipster
  • 2
2 Solutions
 
mirtheilCommented:
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
 
Bill BachPresidentCommented:
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
 
GeekHipsterAuthor Commented:
The only thing I have to say is "Wow!! And they are still in business???"

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

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now