CODEWITHSUNDEEP
oracle-database
ICM
ICM

Reputation: 1

ORA-00923: FROM keyword not found where expected, Reason why I keep getting this error?

Create a view that includes all data for officers, including the total number of crimes in which they participated in filing charges. To speed up the officer queries, store this view data and schedule the data to be updated every two weeks. I do not see why the placement of the FROM is causing this issue, what am I not seeing?

CREATE MATERIALIZED VIEW OFFICERVW
REFRESH COMPLETE
START WITH SYSDATE NEXT SYSDATE + 14
AS 
SELECT officer_id, First, Last, precinct, 
  badge, phone, status, COUNT crime_id count 
FROM officers JOIN crime_officers USING(officer_id)
GROUP BY officer_id, First, Last, precinct, badge, phone, status;

Upvotes: 0

Views: 601

Answers (1)

rainkinz
rainkinz

Reputation: 10394

You have a little syntax error, you need to surround the count with parens:

CREATE MATERIALIZED VIEW OFFICERVW
REFRESH COMPLETE
START WITH SYSDATE NEXT SYSDATE + 14
AS 
SELECT officer_id, First, Last, precinct, badge, phone, status, COUNT(crime_id) as count 
FROM officers JOIN crime_officers USING(officer_id)
GROUP BY officer_id, First, Last, precinct, badge, phone, status;

Upvotes: 2

Related Questions