Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-11-15
20
Medium Priority
?
1,475 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

715 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