RecNo doesn't work properly when used within OnCalcFields

A

aluck

Member
It seems that TOracleDataSet.RecNo works incorrectly in some situations. I have a calculated field (say, 'RecNo') and OnCalcFields event handler like:
OracleDataSet.FieldByName('RecNo').AsInteger := OracleDataSet.RecNo;
Right after dataset opening the RecNo field in TDBGrid looks like a column filled with '1'. While scrolling the grid down values strangely became normal, but simple movement to the end of recordset and then back to the beginning (DataSet.Last, DataSet.First) brings us to the '1'-filled column.

'select rownum, ...' will not help because of 'order by' clause.

Oracle 8i EE, Delphi 5, DOA 3.4.6.1

Have you some suggestion what could be wrong? Thank you for support.

P.S. I've sent a message to support@allroundautomations.nl about a week ago, but still got no responce regardless to my yearly service contract.
 
We received your e-mail, and this is the original reply (which you obviously did not receive):

"I'm not sure why the RecNo property cannot be used in an OnCalcFields event. We'll look into this, but you should be able yo use the rownum pseudo column in the query to obtain a unique sequence number"

You mention here that you cannot use the rownum column because of an order by clause, but this is not immediately obvious to me. Can you clarify this?

------------------
Marco Kalter
Allround Automations
 
Originally posted by mkalter:

You mention here that you cannot use the rownum column because of an order by clause, but this is not immediately obvious to me. Can you clarify this?
Click to expand...
Certainly.

PL/SQL User's Guide and Reference
Release 8.1.6
Part Number A77069-01

"ROWNUM returns a number indicating the order in which a row was selected from a table. The first row selected has a ROWNUM of 1, the second row has a ROWNUM of 2, and
so on. If a SELECT statement includes an ORDER BY clause, ROWNUMs are assigned to the retrieved rows before the sort is done."

The SQL behaviour is the same. My goal is to show just a record no as a first column. While my SQL contains 'order by' clause the values of rownum will be reordered according with 'order by', so instead of record no we will get just the 'unique-row-number', not a record number. For example, for a table like:

FOO BAR
---------
1 A
2 C
3 B

select rownum, FOO, BAR
from buzz
order by BAR

We have a result set like:
rownum FOO BAR
----------------
1 1 A
3 3 B
2 2 C

That is obviously not what I want to get.

Thank you for support
 
I understand, but if you could access the RecNo property in an OnCalcFields event handler, this would have the same value as ROWNUM: a sequence number in the result set.

In other words: RecNo = ROWNUM.

Or am I missing something?

------------------
Marco Kalter
Allround Automations
 
Originally posted by mkalter:
In other words: RecNo = ROWNUM.

Or am I missing something?
Click to expand...
Sure you are. First, there are many RDBMS that doesn't support ROWNUM feature. Second, in such a case there is NO way to show record number for ordered data.

If I ever need just a kind of record identifier it definitely would be a primary key.
smile.gif
 
Originally posted by mkalter:
Just like with RecNo?
Click to expand...
First, I think that RecNo is the only 'legal' way to obtain additional 'No' column in a grid. TTable, TQuery, TRxMemoryData, TFIBDataSet (Free InterBase Components) works in such a way, and all they allow to get .RecNo value in an OnCalcField event handler.

Second, there IS a bug in TOracleDataSet that is easy to reproduce. Getting .RecNo in the OnCalcField will cause incorrect TDbGrid behaviour.

Is it possible to correct this as soon as possible?
 
Originally posted by mkalter:
Of course, I was just trying to provide a workaround.
Click to expand...
I have a service contract with Allround Automations. Does it mean that I should expect to get patches a bit early than a month after bug report?

------------------
 
No, there is no guarantee about the timeframe for bugfixes. It primarily depends on the nature of the bug. I will let you know when we have a fix.

------------------
Marco Kalter
Allround Automations
 
I don't know if it helps you, but note that in 8i and above you can force the ROWNUM pseudocolumn to be in order even if there is an order by clause by putting the query with the order by into an 'inline view'.

A quick example is:
select rownum, t.* from (select * from emp order by ename) t;

I use this a lot since it works so well.

Perhaps that will help you!

-Mark Ford
Benthic Software
 
You must log in or register to reply here.
Back
Top