SQL Oracle LEFT JOIN and SUBQUERY error: ORA-00905: missing keyword
- by Curro
Hello everyone. 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
Thanks a lot!:
DB Tables:
DW.TICKETS
DW.TICKET_ACTLOG
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
