A view or derived table can be flattened into the outer query block if all of
the following conditions are met:
- The select list is composed entirely of
simple column references
and constants.
- There is no GROUP BY clause in the view.
- There is no DISTINCT in the view.
- There is no ORDER BY, result offset, or fetch first clause in the view.
For example, given view v1(a,b):
SELECT Cities.city_name, Countries.country_iso_code
FROM Cities, Countries
WHERE Cities.country_iso_code = Countries.country_iso_code
and a SELECT that references it:
SELECT a, b
FROM v1 WHERE a = 'Melbourne'
After the view is transformed into a derived table, the internal query is
SELECT a, b
FROM (select Cities.city_name, Countries.country_iso_code
FROM Cities, Countries
WHERE Cities.country_iso_code = Countries.country_iso_code) v1(a, b)
WHERE a = 'Melbourne'
After view flattening it becomes
SELECT Cities.city_name, Countries.country_iso_code
FROM Cities, Countries
WHERE Cities.country_iso_code = Countries.country_iso_code
AND Cities.city_name = 'Melbourne'