jassens
|
| Posted: 02/17/2003, 2:26 PM |
|
Hi!
Can anyone help me. I have put custom sql-strings in BeforeBuildSql Event. These works fine, but the CC-Navigation panel count all the records and therefore also calculates a wrong pagecount.
How do I ensure that the Navigation-panel uses the correct recordcount?
With regards
Jorgen
|
|
|
 |
Snorky
|
| Posted: 02/18/2003, 6:22 PM |
|
If you are using GROUP BY in your SQL statements,
this is an error/not supported feature in combination with the Navigation-panel.
Case Info
Case # 983889615
Summary SQL query for grid, navigation to manny pages and records
Description When creating a grid using a SQL query, and using the navigation insirtation, it seems that the navigation is based on the total records processed in the main table.
eg. main table has 10,000 records
SQL query would produse 7 lines in the grid. All fits on the first page.
The Navigation would indicate that there are hundreds of pages, however, when trying to navigate to the next (second) page the message "No more records" will be displayed.
If all lines from the SQL query fits on 1 page there is no problem and I can just remove the Navigation. If not (which also occured in a different case) the only solution I found was to increase the number of pages on a page to the estimated lines in the grid.
It does seem silly if I activate the Navigation then ......
Current Status Closed - Timeout
Posted 10/31/2002 3:54:20 PM
Last Update 12/16/2002 3:24:53 PM
Responses
Posted 11/1/2002 1:08:44 AM
User Helen D.
Status Hold - Proposed Solution
Response Raymond,
Navigator may not work if custom sql includes GROUP BY clause. Let me provide some explanation how count sql is built. The approach is simple, the field listing in SQL is replaced with count(*) preserving the rest part. As you’re using custom sql, the Where and Group By clauses are preserved as well, because they are included into SQL variable (as you can see in generated code). So, after replace you get sql like:
Select count(*) FROM companies
WHERE
company_id = {CompID} AND
description LIKE '%{s_description}%'
GROUP BY item_id
If you run such query you’ll get the not one record in recordset but the same number of records as in normal query. But it is expected that countSQL returns one record only, thus only first one is retrieved. In your case the retrieved vale is 1, that’s why navigator doesn’t work.
To overcome the problem you should use custom query instead generated countSQL like:
Select count(*) from companies where company_id = {CompID} description LIKE '%{s_description}%'
Experiment with different queries against db and find one that works correct.
Overwrite countSQL in the form Before Execute Select.
Please let us know if you need more information or help with it.
Regards,
Helen
|
|
|
|
|
