CODEWITHSUNDEEP
oracle-databasesubqueryoracle-apexoracle12c
Aaheana
Aaheana

Reputation: 1

Why is this query showing error ORA-00923: FROM keyword not found where expected?

I have a sql query, which on execution throws error as :

ORA-00923: FROM keyword not found where expected

Below is the SQL :

SELECT A.Agent_key, Sum(Total_Orders) 

FROM

(SELECT A.Agent_key, Sum(Total_Orders)

     RANK() OVER (ORDER BY Sum(Total_Orders) ASC) AS Rank

     FROM Fact_Order F, Dim_Time T

     WHERE F.Time_Id = T.Time_Id AND T.Quarter= '1' AND

     TO_CHAR(SYSDATE, 'YYYY') = T.Year

     GROUP BY A.Agent_key

)

WHERE Rank < = 1;

Upvotes: 0

Views: 3472

Answers (1)

Popeye
Popeye

Reputation: 35910

  • You are missing one comma after Sum(Total_Orders)
  • You are also missing an alias for subquery (You are using A.Agent_key in outer select clause then A must be an alias of the subquery)
  • group by clause in the outer query is missing. (You are using aggregation so GROUP BY is needed)
  • alias for sum(total_agents) as you are referring to in the outer query

It should be

SELECT A.Agent_key, Sum(Total_Orders) 
FROM
(SELECT A.Agent_key, Sum(Total_Orders) as total_orders,
        RANK() OVER (ORDER BY Sum(Total_Orders) ASC) AS Rnk
   FROM Fact_Order F, Dim_Time T
  WHERE F.Time_Id = T.Time_Id
    AND T.Quarter= '1' 
    AND TO_CHAR(SYSDATE, 'YYYY') = T.Year
 GROUP BY A.Agent_key ) A
WHERE Rnk < = 1
GROUP BY A.Agent_key;

Upvotes: 1

Related Questions