时间戳

首页 > 解决方案 > ORA-00907: 两个子查询的左连接缺少右括号

sql - ORA-00907: 两个子查询的左连接缺少右括号

问题描述

自从我弄乱了 SQL 以来已经有一段时间了，我正在为一门课程重新拾起它。我需要为我的用户的所有表创建一个包含表名、列名、约束名和约束类型的表，但是每次尝试时，都会出现缺少右括号的错误。到目前为止，这是我的代码：

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 说它位于第 4 行的“AS a”，但我在网上找不到太多信息。任何帮助深表感谢！

标签： sqloracle

解决方案

如果您删除 AS 它可以工作：

   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

但我想这个查询可能更短并且没有左连接，因为它总是匹配：

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

推荐阅读