E43509
Posts: 283
|
| Posted: 03/11/2009, 6:35 AM |
|
Looking for ideas on how to stay somewhat within the CCS IDE and not hack it too much.
I have a grid that I want to limit the number of rows returned but also display the total based on the search parameters AND not have to keep re-executing the sql and get that performance hit.
My sample grid sql today:
SELECT name_last, name_first, city, state
FROM people
WHERE state = 'MD'
ORDER BY name_last, name_first
which may return thousands of rows.
To limit the rows (i'm using oracle), I have to do this inline query to ensure top ones come out.
SELECT * FROM (
SELECT name_last, name_first, city, state
FROM people
WHERE state = 'MD'
ORDER BY name_last, name_first
)
WHERE rownum < 100
ORDER BY name_last, name_first
So on my grid, I want the total records to show in essence
SELECT count(*)
FROM people
WHERE state = 'MD'
which I can do as a separate query and build it up from strWHERE clause in the code
And I can do a before execute select change to make CCS strSQL into:
strSQL = 'SELECT * FROM (' & strSQL & ') WHERE rownum < 100
ORDER BY name_last, name_first'
But I was wondering if there is a better way to limit the number of records returned
|
 |
 |
datadoit
|
| Posted: 03/11/2009, 5:12 PM |
|
In your CCS connection properties, there is a setting to limit the row
count. Check that and set it to whatever (typically 100). It's the
same as the SQL LIMIT statement.
Or, just add "LIMIT 100" to the end of your SQL command.
|
|
|
|
|
