2

I can run this flashback query with no problem:

select x from a as of timestamp sysdate;

But if I use a table alias I get an error.

select foo.x from a foo as of timestamp sysdate;
ORA-00933: SQL command not properly ended

How can I use "as of" with table aliases?

3

The table alias follows the "as of" clause.

select x from a as of timestamp sysdate foo;
0

And as an extension if you are joining multiple tables you can join them from different times if you want (not sure why you would want but...)

select *
from a as of timestamp sysdate-1 foo
join b as of timestamp sysdate-2 fi on foo.name = fi.name;
  • Welcome to DBA.SE. That, while correct, isn't directly relevant to the question at hand. Answers should be kept as relevant as possible to the question. Answers that are only asides may cause confusion and pollute search results. As to "why you would want to": joining a table to itself is very common where entities of the same type relate to one another, for example puling back data from person records for an employee and their manager: FROM person underling JOIN person manager ON manager.id = underling.managerId. – David Spillett Sep 14 '18 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.