SQL Oracle Left Join and Subquery Error: Ora-00905: Missing Keyword

SQL Oracle LEFT JOIN and SUBQUERY error: ORA-00905: missing keyword

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

    ) AS TABLE_RESOLVERS 

write

   ) TABLE_RESOLVERS 

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.

SQL Developer doesn't recognize right outer join ORA-00905: missing keyword

Wrong syntax. Try something like this:

select r.*, s.att1, s.att2, s.att3, s.att4
from shell s right outer join route r

Missing Keyword Error - Join Subquery with Oracle

This portion does not make sense:

(SELECT CUST_NO, 
        MIN(CUST_CDE) AS CUSTOMER_CODE 
        FROM SCHEMA.TABLE2
        GROUP BY CUST_NO
) SCHEMA.TABLE2 T2 

It looks like you are giving two aliases to the subquery. But the first one isn't valid.

Your code could have other problems, but try removing the SCHEMA.TABLE2 after the ). I presume you want:

(SELECT CUST_NO, 
        MIN(CUST_CDE) AS CUSTOMER_CODE 
        FROM SCHEMA.TABLE2
        GROUP BY CUST_NO
) T2 

SQL Oracle Error: ORA-00905: missing keyword

The error with what you posted is that the subquery has a group by clause but no aggregate such as sum(), count(), etc. In other words, this is not valid:

select x, y 
from table2 
 group by x, y

Maybe you want

select distinct x, y 
from table2

ORA-00905: missing keyword error oracle

The most likely issue is MJNSXJJRW AS S0. You use the AS keyword when defining column aliases, not when defining table aliases.

CREATE VIEW MJNSXJJRW_view AS  
SELECT B.oID AS "_oid", B.oTm AS "_otm" 
FROM 
(SELECT DISTINCT oID, oTm FROM MJNSXJJRW) B 
LEFT JOIN MJNSXJJRW S0 ON 
    B.oID = S0.oID AND 
    S0.idx = 0 AND 
    S0.kID = "str_val" ; 

I'm assuming that "str_val" is a column, not a string literal. If it's the latter you should use single quotes.

Missing Keyword in JOIN syntax

You have JOIN-WHERE-ON sequence which is wrong.

Should be something like this (assuming WHERE is not a part of your joining condition):

FROM employees e
JOIN jobs j ON e.job_id = j.job_id
....
....
WHERE e.salary >
  (SELECT AVG(salary) FROM employees WHERE job_id LIKE '%CLERK%')
ORDER BY ...

---

Related Topics