Linking tables crashing Pervasive
Linking tables crashing Pervasive
(OP)
Hey all,
Not sure what I am doing wrong with this join for some reason I am unable to link 2 different tables that have a matching linked field.
I used the following queries to make sure that there were matching fields..
(returns 2738 rows - showing customer sales data)
(returns 1 row - showing additional customer information)
But once I try to create a view or even just combine the data via a select statement...
Pervasive just shows me the progress bar until it finally crashes and I have to close the PCC.
What am I doing wrong?
Thanks,
Stan
Not sure what I am doing wrong with this join for some reason I am unable to link 2 different tables that have a matching linked field.
I used the following queries to make sure that there were matching fields..
CODE --> sql
select * from table1 where cust = 27070
(returns 2738 rows - showing customer sales data)
CODE --> sql
select * from view1 where cust = 27070
(returns 1 row - showing additional customer information)
But once I try to create a view or even just combine the data via a select statement...
CODE --> SQL
select * from Table1 left outer join View1 on Table1.Cust = View1.Cust
Pervasive just shows me the progress bar until it finally crashes and I have to close the PCC.
What am I doing wrong?
Thanks,
Stan
RE: Linking tables crashing Pervasive
CODE --> SQL
Mirtheil
http://www.mirtheil.com
RE: Linking tables crashing Pervasive
Thanks for the reply. I'm using Pervasive 11.3,
I tried bypassing the view and linking to the table directly but that didn't have any effect either. Other joins that I have done have worked but just not this one so I was thinking that maybe I was using the wrong join at first but the main table I am trying to link with is a table that has sales by line item and has a few years worth of sales...
I extracted 2018's sales (over 2 million records still) into a new view and tried to join with that with no success but extracting 2019's sales (just over 57000 records so far) worked... Are there any limits on these joins? is there a better way to do something like this with this much data, like combining the 2 tables into a table instead? I would also need it to update with new sales though so I'm not sure how that would work... The problem is I need to make a report that allows me to filter by the criteria that is not in the main table but in that other view (which came from 2 different tables)and that they need the sales by line item instead of by invoice so they can have everything sorted by category and part number.
Thanks again,
Stan
RE: Linking tables crashing Pervasive
Is the Cust field an index on the table (and table in the View)? How many records are you expecting to be returned? Do you have more records in Table1 or View1? If you limit the number of rows in the Join query, does it start working?
Mirtheil
http://www.mirtheil.com
RE: Linking tables crashing Pervasive
I've tried using Crystal Reports, Crystal will bring up the preview for the first page fast once the databases are linked, but it will still take a while for it to get through all of the data and it takes around 10+ minutes to run the report once the report is grouped by part category and part number (I'm guessing because it has to get through all of the data to do it's sorting.)
There is nothing in that log file other than some Frozen and Thawed notifications at night, nothing when the tables are attempting to be compiled. Just the PCC needs to be restarted after it crashes, the engine is still running.
I've been trying to join the data using different tables and views to see if I can find one that is fast but haven't been able to find anything yet. I created 2 views that each has only the columns I need and each of them individually are quick to bring up in Pervasive but stall out until I have to restart PCC once I try to join and do a large query.
View 1 - v__salesWsmsa - Has sales by line item 557,189 rows
View 2 - v__inv_cats - Has inventory and inventory categories 23,124 rows
I need to get the two views joined by part number so I can add the item categories to the sales data but that is when the query is timing out, either in Pervasive or in Crystal
I tried to run the query using a single part and the query was able to complete (returning 1796 rows of data) but when I try to run it for everything then thats when it is unable to complete.
The query I tried is...
CODE --> SQL
Thanks,
Stan
RE: Linking tables crashing Pervasive
What indexes are on the tables you are trying to join? That would be an issue. Have you used the Query Plan Viewer to see what the query is doing? Here's a link to an old article about Query Plan Viewer. Here's a link to the PSQL v13 documentation on QUery Plan Viewer. It should be similar enough to get you started with it.
Mirtheil
http://www.mirtheil.com
RE: Linking tables crashing Pervasive
CODE --> SQL
and there werent millions of additional rows being created causing my query to even stall out overnight.
Thanks again for everything!
Stan
RE: Linking tables crashing Pervasive
Mirtheil
http://www.mirtheil.com