How to suppress exceeding number of records

[H303]

Is there any way how to limit the number of returned records to a certain value? I have a table which holds millions of measurement data and timestamps. When I do a select for a certain time range, I may get back thousends of records and I only need to present the first 25 for a preview.
Functions like:
SELECT ... ... WHERE ROWNUM <= 25 (Oracle)
or
SELECT FIRST 10 * (Informix)
did not work with Paradox. My programming environment is Borland C++ Builder.
Many thanks for every hint!
Hermann

 

[Langley]

Are you using C++ to access Paradox tables via ODBC, or Paradox? If Paradox, then if you had no way to limit the data in the query using example elements, you would have to use a scan loop to process the results table and copy the first 25 records into a holding table using the copyToArray() method. If you are using C++, then you might have better luck in an SQL or C based forum. Mac

&quot;There are only 10 kinds of people in this world... those who understand binary and those who don't&quot;

langley_mckelvy@cd4.co.harris.tx.us

 

[H303]

Hi Mac,
many thanks for your fast response. Yes I am using Borland C++ Builder and and are acessing Paradox via BDE.
Hm, so it looks like I do have to do some coding to circumvent the lack of commands like '.. WHERE ROWNUM <= 25' or 'SELECT FIRST ...' from other SQL dialetcs.
Best regards, and again many thanks for your help.
Hermann

 

[footpad]

H303,

First off, this particular forum is designed to support Corel's Paradox for Windows database software. It's not meant to provide general service for Borland's Paradox file format. In general, it's best to post messages directly related to the programming tool you're working with.

Please don't misunderstand me, we're not trying to be mean. It's just that there are a lot of tools you can use to work with Paradox tables. No one can use them all, so we try to keep people organized by tools, rather than tasks.

Having said that, I don't believe Borland ever extended LocalSQL to provide a row limiting predicate for local database tables such as the ones you've mentioned.

Given that, you need to build this capability into your database tables, perhaps by queries to dermine ranges for matches, adding categorical limits, or even modifying your underlying queries to limit results based on common data elements.

Now, it's entirely possible there's a &quot;super secret squirrel&quot; feature that was added for this. After all, you've been able to use subqueries for INSERT, UPDATE, and DELETE queries against local (Paradox and dBASE) for some time, but that's only been documented in the last couple of revisions to LOCALSQL.HLP. However, if such a feature exists, I've not been able to find a trace of it.

Also, it's entriely possible that there's a way to connect to Paradox tables through ADO, dxExpress, or some other provider mechanism that supports row limiting predicates. I don't believe there is, however, I'll admit that I've not studied these alternatives extensively in my Delphi work. Again, this would be something better asked in the C++ Builder forum (

http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/29/pid/101).

Sorry...

-- Lance

 

[Langley]

I'm by no means an SQL guy. However, Paradox OPAL refers to Recno instead of Rownum, perhaps you might try a substitution of the word. Just a wild ass guess. Mac

&quot;There are only 10 kinds of people in this world... those who understand binary and those who don't&quot;

langley_mckelvy@cd4.co.harris.tx.us

 

[footpad]

Mac,

I'm afraid that won't work with C++ Builder. RecNo is simply the ObjectPAL method (and property) that Borland used to surface the underlying record number property of the Paradox BDE driver.

In this case, I'm afraid there isn't a documented SQL approach, other than one you might build into the data itself. (Perhps it would be possible to add a field/table that acts like a secondary index and tracks the position of key value(s) in the table. That's essentially what a secondary index does, only you'd be maintaining this manually, probably when the application starts or undergoes major updates.

It's would be a bit of work, but since both C++ Buildaer and Delphi create true .EXE's, they would update this far more quickly than Paradox for Windows could, probably 20-50 times faster.

It would still be a table scan, but I wouldn't expect it to take too long. Indeed, you might even be able to update it as part of the query process.

A lot depends on the actual number of records, any existing indexes, network performance time, and other related factors.

Hope this helps...

-- Lance