Oracle Flashback – Using ‘As Of’ Clause with Table Aliases

flashbackoracle

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?

Best Answer

The table alias follows the "as of" clause.

select x from a as of timestamp sysdate foo;

Related Solutions

Oracle – Alias Names and Two Table Query Problems

Oracle does not support using the AS keyword for defining a table alias, only for column aliases. Just rewrite the query by placing a space between the table name and the alias and see if it runs that way.

In particular rewrite the FROM clause like this:

 FROM anc_test_codes ats,
   anc_results_seqs ars

Sql-server – Oracle With Statement and Delete Together

The Oracle syntax is DELETE FROM (query). The query can be a SELECT with subquery factoring:

delete from (
   with to_delete as (
      select * from a
   ) 
   select * from to_delete 
);

The above query works in 11gR2 but fails with ORA-01732 (non-updateable view) on an old 9iR2.

Best Answer

Related Solutions

Oracle – Alias Names and Two Table Query Problems

Sql-server – Oracle With Statement and Delete Together

Related Question