Can I use Limit command in a query for a Paradox table

Hi
Unfortunately I am still maintaining a system written in Delphi that uses Paradox database
Until the system is revamped or rewritten,  to use Mysql or MSSQL
I have a major problem with limits in a  query statements
Can anybody advise me if it is possible to use the limit command in my query similar to a Mysql  :
Select * from database_name limit 0,1000
Thanks you
SSSIANAsked:
Who is Participating?
 
jimyXConnect With a Mentor Commented:
It is not available in Paradox DB. When you use it, it gives Error "invalid use of keyword"
0
 
ThommyCommented:
Syntax of SELECT-statement in Local SQL:

SELECT [DISTINCT | ALL] * | column
[AS correlation_name | correlation_name], [column...]

[INTO destination_table]

FROM table_reference
[AS correlation_name | correlation_name] [EXCLUSIVE]

[[[[INNER | [LEFT | RIGHT] OUTER JOIN] table_reference
[AS correlation_name | correlation_name] [EXCLUSIVE]
ON join_condition]

[WHERE predicates]

[GROUP BY group_list]

[HAVING predicates]

[[UNION | EXCEPT| INTERSECT] [ALL] [SELECT...]]

[ORDER BY order_list [NOCASE]]

[TOP number_of_rows]

[LOCALE locale_name | LOCALE CODE locale_code]

[ENCRYPTED WITH password]

[NOJOINOPTIMIZE]
[JOINOPTIMIZECOSTS]

Open in new window

0
 
ThommyCommented:
You can select the first n rows by using TOP-clause:

Select * from database_name TOP 1000


0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
SSSIANAuthor Commented:
Hi Thommy
Select * from database_name TOP 1000

gives me invalid use of keyword error


Regards
0
 
ThommyCommented:
Try this:

SELECT TOP 1000 * from database_name
0
 
SSSIANAuthor Commented:
Hi Thommy

SELECT TOP 1000 * from database_name  gives me the error

Regardd
0
 
ThommyCommented:
Tell me the error message!!!
0
 
jimyXCommented:
It will not work in Paradox, the paradox SQL has no such commands. The only way to exclude records is by eliminating them in where condition "Where in (), =, <, > ,...".
0
 
SSSIANAuthor Commented:
Hi Thommy
Using 'Select * from database_name TOP 1000'
I get the following message
'Invalid use of keyword'
Token : top

But if have
'select  * from Supplier top'  I get no error, but it shows all 2000 records

on Using
SELECT TOP 10 * from database_name
I get the following message
'Invalid use of keyword'
Token : 10
regards
0
 
jimyXCommented:
SSSIAN,

Even when you use:
'select  * from Supplier limit' you will not get error;
And when you write:
'select  * from Supplier SSSIAN'  also you will not get any error (haven't checked this one).

But when you add more like limit 0 or limit 0, 1000 you start getting "invalid use of keyword"
0
 
ThommyCommented:
What about this:
SELECT TOP(10) * from database_name
0
 
Ephraim WangoyaCommented:
There is no support for features such as TOP or LIMIT in paradox

Filter you data using your primary key for the table

select *
from table1
where table1.ID < 1000
0
 
SSSIANAuthor Commented:
Hi All
Thanks for all you help
There seems no solution to this
Hence the urgency to migrate to Mysql or MSSQl
Thanks
0
 
ThommyConnect With a Mentor Commented:
There's already a question concerning the same issue in EE, which did not lead to a solution:

paradox SQL syntax
Does anyone know what the SQL statement to  bring only specified number of rows in paradox table? I know that in DB2 you can use "FETCH N Rows only" statement
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_22656093.html

I have tested all I could think of, but did not find anything like TOP or LIMIT in local sql.

So I subscribe to ewangoya's view to limit your selection by using the primary key of your paradox table...
0
 
jimyXCommented:
My advice is to move to an advanced Database which will save your time and future troubles.
0
 
SSSIANAuthor Commented:
Hi Thommy
Please correct me if I am wrong
I have a database with 30000 records
I might have query where I state
Select * from table1 where field1=%D
select * from Table1 where Field  like 'G%'

using  table1.ID < 1000 will not help me because I could have 1 to N records for my search where table1.ID > 1000
regards


0
 
Ephraim WangoyaCommented:
@SSSIAN

That was just an example not a solution. The idea is that you have to filter with one of your fields.
Migrating is a good option and there are good free databases out there that are supported by delphi. As you mentioned MySQL, you can also try Firebird
0
 
ThommyCommented:
If you have for example a date column in your table, you can restrict the number of retrieved rows to recent dates not older than x days...
0
 
ThommyCommented:
Get only rows, which are not older than 10 days...
with Query1 do begin
  if active then close;
  Params[0].AsDate := Sysutils.Date-10;
  Active := TRUE;
end;

The SQL statement of the Query1 using the parameter is:

select * from Table_Name where Date_Field > :Param1

Open in new window

0
 
SSSIANAuthor Commented:
The answer was correct, there is no solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.