• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1866
  • Last Modified:

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
0
SSSIAN
Asked:
SSSIAN
  • 8
  • 6
  • 4
  • +1
2 Solutions
 
jimyXCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
 
ThommyCommented:
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 8
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now