Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to SELECT COUNT(*) first before returning SELECT *

user1360631Apr 25 2014 — edited May 5 2014

Our frontend involves a grid that is populated by a SELECT query. The records are retrieved via a complex query with many tables involved and many filtering parameters passed. A simplified example:

SELECT col1, col2...colx

FROM tab1 t1, tab2 t2, tab3 t3, etc.

WHERE

   t1.key1 = t2.key1

   and t2.key2 = t3.key2,

   and ... etc.

   and t1.coldate > p_FilterDateFrom

   and t1.coldate < p_FilterDateTo

   and t2.somefield = p_FilterSomeFilter

   and t3.someotherfield = p_FilterSomeOtherField

   and ... etc.

Usually, the user will enter parameters so a small, manageable resultset is returned.

But occasionally, the user will enter parameters that will return thousands of rows. As a result, the frontend within IE pauses from time to time saying "Stop running this script? A script on this page is causing your web browser to run slowly..."

I'd like to execute the above example query with a SELECT COUNT(*) first before executing the actual SELECT col1, col2...col3. This way if the returned rows exceeds a certain threshold, say, 500 rows, I could pop a message saying "Over 500 rows will be returned. Do you wish to continue?". If the user clicks yes, then I go ahead and run the actual query and populate the grid. If the user clicks no, then the user gets a chance to fine-tune his/her parameters.

Is there an easy way to do this short of coding two versions of the same complex query, one for "SELECT COUNT(*)" and one for (essentially) "SELECT *" ?

Thank you.

This post has been answered by Greg Spall on Apr 25 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 2 2014
Added on Apr 25 2014
13 comments
2,292 views