Solved

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

Posted on 2010-11-15
20
1,208 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 175 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 19

Expert Comment

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

Expert Comment

by:ewangoya
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 75 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:ewangoya
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This article describes some very basic things about SQL Server filegroups.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now