SQL Oracle LEFT JOIN 和 SUBQUERY 错误:ORA-00905:缺少关键字

2022-01-23 00:00:00 sql left-join subquery oracle ora-00905

就这个 ​​Oracle 查询寻求您的帮助.它给了我错误 2ORA-00905:缺少关键字".在我添加 LEFT JOIN 语句之前它工作正常.显然，如果没有 LEFT JOIN 语句，它将无法提供我们需要的信息.

Asking for your help on this Oracle query. It's giving me the error 2 "ORA-00905: missing keyword". It was working fine before I added the LEFT JOIN statement. Obviously it won't deliver the information as we need it without the LEFT JOIN statement.

请提供任何帮助以了解此查询中缺少哪个关键字

Please provide any help to know which keyword is missing in this query

非常感谢！:

数据库表:DW.TICKETSDW.TICKET_ACTLOG

DB Tables: DW.TICKETS DW.TICKET_ACTLOG

子查询表:TABLE_RESOLVERS

Subquery table: TABLE_RESOLVERS 

SELECT 
    TO_CHAR(DW.TICKETS.RESOLVED_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') AS RESOLVED_DATE, 
    DW.TICKETS.SUBJECT, DW.TICKETS.OWNER_CORE_ID, 
    DW.TICKETS.TICKET_NUMBER, 
    TABLE_RESOLVERS.SUBMITTER AS RESOLVER_CORE_ID 

FROM DW.TICKETS 

LEFT JOIN
    (SELECT 
        TICKET_NUMBER,
        SUBMITTER 
    FROM DW.TICKET_ACTLOG 
    WHERE 
        TYPE = 'Final Resolution' AND 
        (SUBMITTER = 'B02666' OR 
        SUBMITTER = 'R66604') 
    ORDER BY CREATE_TIMESTAMP DESC 
    ) AS TABLE_RESOLVERS 

ON DW.TICKETS.TICKET_NUMBER = TABLE_RESOLVERS.TICKET_NUMBER  

WHERE 
    DW.TICKETS.RESOLVED_TIMESTAMP >= to_date('05-03-2010','dd-mm-yyyy') AND 
    DW.TICKETS.RESOLVED_TIMESTAMP < to_date('8-03-2010','dd-mm-yyyy') AND 
    DW.TICKETS.TICKET_NUMBER LIKE 'TCK%' AND 
    DW.TICKETS.TICKET_NUMBER IN 
        (SELECT TICKET_NUMBER 
        FROM DW.TICKET_ACTLOG 
        WHERE 
            (SUBMITTER = 'B02666' OR 
            SUBMITTER = 'R66604') 
        ) 

ORDER BY DW.TICKETS.CREATE_TIMESTAMP ASC

推荐答案

在 Oracle 中，我们在声明表别名时不包含 AS.而不是

In Oracle we don't include the AS when declaring a table alias. Instead of

    ) AS TABLE_RESOLVERS

写

   ) TABLE_RESOLVERS

这是 Oracle 语法比其他一些 SQL 风格更具限制性的一个示例.它也与列别名的声明不一致，这很不幸，但几乎可以肯定的是，在以后改变这一点太复杂了.

This is one example when Oracle syntax is more restrictive than some other flavours of SQL. It is also inconsistent with the declaration of column aliases, which is unfortunate but almost certainly it's too complex to change this far down the road.

相关文章