Expert: Jean-Pierre Zuate - 12/5/2006

Question
Hi, I would read the deleted records of a tablefield in the order in which they were deleted. The UNLOADTABLE statement processes deleted record in the reverse order in which they were deleted. Can you help me?

Answer
Hello,

Yes I hope I can help you ...
Let me remember some knowledge :
1/ _state = 4 if a row is deleted
2/ _record = _record * -1 when a row is deleted

It mean, for example, if you have a tablefield with 4 rows :
1/ You delete row 3. The _state become 4 and _record become -3
2/ You insert again row 3. The _state is 1 and _record is 3
3/ You delete again row 3. The _state of this new row is 4 and _record become -3 also ...

From Ingres2006 documentation (download it from www.ingres.com) see a PDF named fadt.pdf (Form-Based Application Development Tools User Guide), page 16-2 from the index table (page 574 of the real PDF file). It show a diagram explaining that ...

So you can imagine to read the tablefield in the correct order is impossible without manipulation.

What you need is to maintaint an hidden column with a chronologic data (sequence or date & time), then load it in a Global Temporary Table (for example) and then do a SELECT on this session table in the order you need.

When I say chronologic with a sequence I refer to CREATE SEQUENCE (see SQL.PDF).
When I talk about Global Temporary Table I refer to DECLARE GLOBAL TEMPORARY TABLE (see SQL.PDF again).

Hope this help.

Regards,
Jean-Pierre ZUATE
La Fage Conseil
+33(0)6 1140 1109