Refer to other SQL SELECT statements aliased as tables in FROM clause

Question

I have a very large query that follows the format below:

select ...
  from ( select field1,
                field2
           from some_table ) table1,
       ( select field1,
                field3
           from other_table ) table2

 where .....

Is is possible for me to refer to one of the tables "defined" in the from clause, lets say table1, in one of the other table definitions in the from clause?

For example:

select ....
  from ( select field1,
                field2
           from some_table ) table1,
       ( select table1.field1,
                field3
           from other_table,
                table1 ) table2

 where .....

Disclaimer: What I am trying to do is not as simple as the example above. The example is simply to illustrate the idea.

Answer 1

WITH
table1 AS
        (
        SELECT  field1, field2
        FROM    some_table
        ),
table2 AS
        (
        SELECT  field1, field2
        FROM    other_table, table1
        )
SELECT  *
FROM    table2

Answer 2

If you are using SQL 2005, you can use Common Table Expressions for doing what you are trying; Quassnoi gives us an example, in Oracle I don't know how to achieve it though