Question How to limit select result rows in Progress 9.1D09?

[cja]

I'm trying to extract data from a Progress 9.1D09 database. I understand that this isn't the latest version of Progress but upgrading isn't an option. The database is used by a dying program and I am moving the data to its successor.

One table has 162000 rows. I want to work with a small number of rows.

In SQL Server I would change my query to "select top 100 * from ...". In MySQL I would do "select * from ... limit 0,100".

Neither of these syntaxes work and googling for the right syntax has failed me so far.

How can I limit the number of rows in the source data using SQL?

 

[Rob Fitzpatrick]

You may want to start with the Progress 9.1D documentation and look at the "SQL-92 collection".

 

[tamhas]

TOP was added at some point, but you would need to upgrade to get it.

 

[maretix]

TOP was added at some point, but you would need to upgrade to get it.

Good morning Mr. TAMHAS ..

I have a similar problem with numer of rows ..
My problem is to UPDATE only a certain numbers of rows of a PROGRESS Table of 1.000.000 records...

A third party program need to connect to our DB Progress and update a Progress Table ..

update table set field = 'value

When they run this query , even if DB Parameter -L is set currently correct ..they recive the below error :

ODBC -2146232009 ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Failure getting record lock on a record from table PUB.e7mopro.

So i ask if it is possible to solve this problem ...

Can do i solve that with SQL ODBC ...???
Is there a chance to read only a certain number of records time by time ????
Something like that a loop that can update 3000 records and then other 3000 and then other 3000 ....

I wrote 3000 records because with 3000 we received NO ERRORS.
With more records we receive ERRORS.

Regards.

 

[maretix]

TOP was added at some point, but you would need to upgrade to get it.

In short words , i'd like to do the same SQL Command (sql server) in SQL ODBC Progress OpenEdge ..IS IT POSSIBILE ???

WHILE EXISTS ( SELECT * FROM table WHERE <condition to update>)
BEGIN
SET ROWCOUNT 1000
UPDATE Table WHERE <Condition to update>
SET ROWCOUNT 0
END.

Many thanks.

 

[TomBascom]

Yes it is possible.

But probably not on such an ancient, obsolete and unsupported platform as 9.1D.

 

[maretix]

Yes it is possible.

But probably not on such an ancient, obsolete and unsupported platform as 9.1D.

Hi Mr To Bascom.
I have 10.2.b OpenEdge Version...is it possibile according your opinion ???
Do you know where can i find useful examples or guide about it ???
Regards.

 

[TomBascom]

Have you made any attempt at all with 10.2B?

Have you read the documentation?

If you have done these things -- What does your code look like and results or errors did you get? What part of the documentation is unclear or wrong or doesn't meet your needs?

 

[maretix]

Hi Tom.
I did tests with 10.2B ...the same ..i got error i posted.
I read documentation (the same you kindly posted) but i did not find anything of useful to solve my problem.

My problem is to find a way to read only a number of records , doing a loop , to override the problem of LOCK ...
Using ABL i know how to do,,,in SQL no...the problem is not mine but about a hird part program that need to update a very large table only with UPDATE SQL Statement...
I thinked it was possible to do that with SQL...

Regards.

 

[maretix]

In short words , i'd like to do the same SQL Command (sql server) in SQL ODBC Progress OpenEdge ..IS IT POSSIBILE ???
WHILE EXISTS ( SELECT * FROM table WHERE <condition to update>)
BEGIN
SET ROWCOUNT 1000
UPDATE Table WHERE <Condition to update>
SET ROWCOUNT 0
END.
Many thanks.

 

[TomBascom]

In general -- yes it is possible to return X number of rows. That is what TOP is for. Have you tried it yet? Tamhas suggested it quite a while ago and it is covered on page 49 of the referenced documentation. I do not see it in use in any of your posted examples.

In your posted examples you appear to be attempting to use T-SQL syntax. That isn't going to work. There are many different dialects of SQL and using the non-standard syntax of one vendor (in this case Microsoft) to access another vendor's DB isn't going to work.

The error that you seem to be referring to is obviously NOT related to having tried any of these suggestions. That lock table overflow error is from your original attempt to update all the records. It does not show that you have tried to use TOP or otherwise attempted any of the suggestions that have been offered.

 

[TomBascom]

Also -- I am glad to hear that you have 10.2B (although I'd be even happier to hear that your have 11.3) but how did you get from "upgrading isn't an option" to "I have 10.2B"? Are you attempting to use a 10.2B client to extract data from a 9.1D database?

 

[TomBascom]

You may also be interested to know that the OpenEdge 11.3 release notes say:

OpenEdge SQL now provides support for limiting the result set of queries through a session wide parameter ROWCOUNT.
Accordingly, support for two new statements, SET ROWCOUNT and SHOW ROWCOUNT, is added to OpenEdge SQL

 

[TomBascom]

And the 11.2 release notes mention:

OpenEdge SQL Release 11.2 includes the following features:
• A new query paging solution using OFFSET and FETCH clauses that allows you to filter:
– The requested range of rows based on the given ordering
– A number of rows to skip and a number of rows to return