Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-11-15
20
Medium Priority
?
1,559 Views
Last Modified: 2013-11-23
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
Comment
Question by:SSSIAN
  • 8
  • 6
  • 4
  • +1
20 Comments
 
LVL 24

Accepted Solution

by:
jimyX earned 700 total points
ID: 34135756
It is not available in Paradox DB. When you use it, it gives Error "invalid use of keyword"
0
 
LVL 19

Expert Comment

by:Thommy
ID: 34136403
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
 
LVL 19

Expert Comment

by:Thommy
ID: 34136409
You can select the first n rows by using TOP-clause:

Select * from database_name TOP 1000


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SSSIAN
ID: 34136531
Hi Thommy
Select * from database_name TOP 1000

gives me invalid use of keyword error


Regards
0
 
LVL 19

Expert Comment

by:Thommy
ID: 34136595
Try this:

SELECT TOP 1000 * from database_name
0
 

Author Comment

by:SSSIAN
ID: 34136669
Hi Thommy

SELECT TOP 1000 * from database_name  gives me the error

Regardd
0
 
LVL 19

Expert Comment

by:Thommy
ID: 34136712
Tell me the error message!!!
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34136742
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
 

Author Comment

by:SSSIAN
ID: 34136854
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
 
LVL 24

Expert Comment

by:jimyX
ID: 34136966
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
 
LVL 19

Expert Comment

by:Thommy
ID: 34136989
What about this:
SELECT TOP(10) * from database_name
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34138606
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
 

Author Comment

by:SSSIAN
ID: 34145033
Hi All
Thanks for all you help
There seems no solution to this
Hence the urgency to migrate to Mysql or MSSQl
Thanks
0
 
LVL 19

Assisted Solution

by:Thommy
Thommy earned 300 total points
ID: 34145116
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
 
LVL 24

Expert Comment

by:jimyX
ID: 34145161
My advice is to move to an advanced Database which will save your time and future troubles.
0
 

Author Comment

by:SSSIAN
ID: 34145262
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34145369
@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
 
LVL 19

Expert Comment

by:Thommy
ID: 34145739
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
 
LVL 19

Expert Comment

by:Thommy
ID: 34145923
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
 

Author Closing Comment

by:SSSIAN
ID: 34267563
The answer was correct, there is no solution
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question