Always enclose the select statement of a subquery in parentheses. The select syntax for a subquery is somewhat restricted:
(select [all | distinct] subquery_select_list [from_clause] [where search_conditions] [group by aggregate_free_expression [, aggregate_free_expression]...] [having search_conditions])
from_clause ::= from table_reference [,table_reference]...
table_reference ::= table_view_name | ANSI_join
table_view_name ::=
{table_view_reference | derived_table_reference}
[holdlock | noholdlock]
[shared]
table_view_reference ::=
[[database.]owner.]{table_name | view_name}
[[as] correlation_name ]
[index {index_name | table_name }]
[prefetch size ]
[lru | mru]}}
derived_table_reference ::= derived_table [as] correlation_name [‘(‘ derived_column_list’)’]
derived_column_list ::= column_name [’,’ column_name]
derived_table ::= ’(’ select ’)’
