Correlated SQL derived tables

Correlated SQL derived tables, which are not ANSI standard, are not supported. For example, the following query is not supported because it references the SQL derived table dt_publishers2 inside the derived table expression for dt_publishers1:

select * from 
   (select * from titles where titles.pub_id =
      dt_publishers2.pub_id) dt_publishers1,
   (select * from publishers where city = "Boston")
      dt_publishers2
where dt_publishers1.pub_id = dt_publishers2.pub_id

Similarly, the following query is not supported because the derived table expression for dt_publishers references the publishers_pub_id column, which is outside the scope of the SQL derived table:

select * from publishers
  where pub_id in (select pub_id from 
                      (select pub_id from titles
                      where pub_id = publishers.pub_id)

                   dt_publishers)

The following query illustrates proper referencing and is supported:

select * from publishers
  where pub_id in (select pub_id from
                      (select pub_id from titles)
                   dt_publishers
                     where pub_id = publishers.pub_id)

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF