SQL Oracle LEFT JOIN 和 SUBQUERY 错误: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.
相关文章