Problem wih a line of code.

Question

Greetings, I am having problems with a line of code. I am trying to create a count function for a view that I created. I have done this a bunch of different ways but below is the format that I have most recently used.

CREATE VIEW ERP_REPORT(EVENTNAME, DESCRIPTION, COUNT(RIDERS) AS
SELECT EVENTNAME, RACES.DESCRIPTION, 
       RIDERS_FIRSTNAME||' '||RTRIM(RIDERS_LASTNAME)
FROM EVENTS, RACES, PARTICIPATION, RIDERS
WHERE EVENTS.EVENTID = RACES.EVENTID
AND RACES.RACEID = PARTICIPATION.RACEID
AND RIDERS.RIDERID = PARTICIPATION.RIDERID
ORDER BY RIDERS.RIDERS_LASTNAME, EVENTNAME;

The error I am getting is ORA-00907: missing right parenthesis the error is at (COUNT(RIDERS) part of the code. Any ideas how I should tackle this the reason would be very hepful.

Answer 1

CREATE VIEW ERP_REPORT(EVENTNAME, DESCRIPTION, COUNT(RIDERS) AS

....

Should not it be:

CREATE VIEW ERP_REPORT(EVENTNAME, DESCRIPTION, COUNT(RIDERS)) AS

Answer 2

As the error message points out you are missing a closing bracket ). The bracket is opened here : 'ERP_REPORT(EVENTNAME' and is never closed.

Answer 3

Hi Michael,

you can't have brackets in column names unless you put quotation marks around. Try this:

CREATE VIEW ERP_REPORT(EVENTNAME, DESCRIPTION, "COUNT(RIDERS)") AS ...

or

CREATE VIEW ERP_REPORT(EVENTNAME, DESCRIPTION, COUNT_RIDERS) AS ...

For example:

SQL> CREATE OR REPLACE VIEW foo ("count(*)") AS SELECT COUNT(*) FROM dual;

View created

SQL> CREATE OR REPLACE VIEW foo (count_all) AS SELECT COUNT(*) FROM dual;

View created

Answer 4

The list of names in parentheses on line 1 should be the names of the view columns:

CREATE VIEW ERP_REPORT(EVENTNAME, DESCRIPTION, COUNT(RIDERS) AS ...

You can't create a column called "COUNT(RIDERS" or even "COUNT(RIDERS)" since a column name may not contain ( or ). This would work:

CREATE VIEW ERP_REPORT(EVENTNAME, DESCRIPTION, RIDER_FULL_NAME) AS ...

However it appears that you really do want a count of something, though I'm not sure of what. To do that the view definition would have to be something like:

CREATE VIEW ERP_REPORT(EVENTNAME, DESCRIPTION, RIDER_COUNT) AS
SELECT EVENTNAME, RACES.DESCRIPTION, COUNT(*)
FROM EVENTS, RACES, PARTICIPATION, RIDERS
WHERE EVENTS.EVENTID = RACES.EVENTID
AND RACES.RACEID = PARTICIPATION.RACEID
AND RIDERS.RIDERID = PARTICIPATION.RIDERID
GROUP BY EVENTNAME, DESCRIPTION;

(i.e. the COUNT function goes in the SELECT part, not in the list of column names).

As an aside, since you are presumably new to Oracle, I would suggest you start using the more modern ANSI join syntax to make your queries clearer:

...
FROM EVENTS
JOIN RACES ON RACES.EVENTID = EVENTS.EVENTID
JOIN PARTICIPATION ON PARTICIPATION.RACEID = RACES.RACEID
JOIN RIDERS ON RIDERS.RIDERID = PARTICIPATION.RIDERID

Answer 5

As Tony points out there are actually several syntax errors in your statement. The missing bracket is just the first.

I find it useful to have an IDE which supports bracket matching, because it can be hard to walk back through the code and find which bracket lacks a mate. As it happens my choice is TextPad but just about anything which is more advanced than NotePad ought to be able to do this.