ORA-00907: Missing Right Parenthesis on a left join of two subqueries

It’s been a little while since I’ve messed with SQL and I’m picking it back up for a course. I need to create a table of table name, column name, constraint name, and constraint type for all tables with my user, but every time I try, I’m getting a missing right parenthesis error. Here’s my code so far:

SELECT table_name, column_name, constraint_name, constraint_type 
FROM     
(
     SELECT a.table_name, a.column_name, a.constraint_name FROM all_cons_columns AS a
     WHERE a.owner = '[my_user]'
     AND a.table_name NOT LIKE 'APEX%'
     AND a.constraint_name NOT LIKE 'BIN%'
     ORDER BY a.table_name
) AS x
LEFT JOIN
(
     SELECT b.constraint_name, b.constraint_type FROM all_constraints AS b
     WHERE b.owner = '[my_user]'
     AND b.table_name NOT LIKE 'APEX%'
     AND b.constraint_name NOT LIKE 'BIN%'
     ORDER BY b.constraint_name
) AS y
ON x.constraint_name = y.constraint_name

SQL Developer says it’s at the “AS a” in line 4 but I’m not finding much information scouring the web. Any help is much appreciated!

Answer

If you remove AS it works:

   SELECT table_name, column_name, x.constraint_name, constraint_type
      FROM (SELECT a.table_name, a.column_name, a.constraint_name
              FROM all_cons_columns  a
             WHERE a.owner = '[my_user]'
               AND a.table_name NOT LIKE 'APEX%'
               AND a.constraint_name NOT LIKE 'BIN%'
             ORDER BY a.table_name) x
              LEFT JOIN (SELECT b.constraint_name, b.constraint_type
                    FROM all_constraints b
                   WHERE b.owner = '[my_user]'
                     AND b.table_name NOT LIKE 'APEX%'
                     AND b.constraint_name NOT LIKE 'BIN%'
                   ORDER BY b.constraint_name) y
          ON x.constraint_name = y.constraint_name

but I suppose this query might be shorter and without left join, because it’s always matches:

select x.table_name,
       x.column_name,
       x.constraint_name,
       y.constraint_type
  from all_cons_columns x
  join all_constraints y
    on x.constraint_name = y.constraint_name
 where x.owner = '[my_user]'
   and x.owner = y.owner
   and x.table_name NOT LIKE 'APEX%'
   and x.constraint_name NOT LIKE 'BIN%'
 order by x.table_name, x.constraint_name