Hi reader,

changing a type of a company form internal value 2 to 1 increases the number companies with type 1; Ok thats no rocket science. But i wanted to run the counting query in the past to get the old number companies with company type = 1;

Currently i get following result with 359 Companies :

SQL> SELECT COUNT(*)
  2            FROM Company
  3           WHERE Company.Companytype = 1
  4  ;

  COUNT(*)
———-
       359

with following query i get the result in the past (358 Companies). Undo Blocks were still available for this, look at the AS OF TIMESTAMP Clause:

SELECT *
  FROM (SELECT COUNT(*)
          FROM Company
         WHERE Company.Companytype = 1) AS OF TIMESTAMP SYSDATE - 0.1;

 SQL> SELECT *
  2    FROM (SELECT COUNT(*)
  3            FROM Company
  4           WHERE Company.Companytype = 1) AS OF TIMESTAMP SYSDATE - 0.1;

  COUNT(*)
———-
       358

SQL>

For a limited time you could query in the past even with COUNT(*)  - Queries;

HTH Karl

PS.: Unfortunately this works only for the past but not for the future - ;-)