ORA-00907: missing right parenthesis

Toad Data Point
1

Hi,

Bit of a novice at all this and they just fired the guy who built the database, so no luck there. I have created a query using Query Builder and slightly editing it. I’m trying to count activity over a date range by the month the record was created (“DPS_USER”.REGISTRATION_DATE). So if in Mar-07 574 records match my WHERE statement, how many from Mar-07 still match statement in Apr-07, May-07 etc.

Also want to drill down by the type of record they are ("IN_USEREXT".CATEGORYNEW)

Month
Mar-07
Apr-07
May-07
Jun-07
Mar-07
574
163
141
(“DPS_USER”.LOGIN)), (“IN_USEREXT”.CATEGORYNEW)

CROSS JOIN

XXXX.ACTIVITY_LOG_URL "ACTIVITY_LOG_URL"

CROSS JOIN

INNER JOIN

ON (“IN_USER”.ID = “DPS_USER”.ID))

XXXX.IN_USEREXT "IN_USEREXT"

WHERE TRUNC(“DPS_USER”.REGISTRATION_DATE BETWEEN to_date (‘01/01/2009’, ‘dd/mm/yyyy’)

GROUP BY TO_CHAR((“DPS_USER”.REGISTRATION_DATE, ‘Mon-YY’), (“ACTIVITY_LOG”.LOG_TIME, ‘Mon-YY’)),“IN_USEREXT”.CATEGORYNEW

AND (“ACTIVITY_LOG”.ACTION IN (4,5)) OR (“ACTIVITY_LOG”.ACTION = 0 AND “ACTIVITY_LOG_URL”.FILENAME LIKE (‘lm_transcript.php’, ‘transcript.php’))

ORDER BY “DPS_USER”.REGISTRATION_DATE DESC

       100

Getting the error ORA-00907: missing right parenthesis with the below query. Tried removing the trunc and to_char statements around the dates as well addng/substracting brackets, but I just keep getting the same error code.

Where should the darn missing right parenthesis go? Here’s the SQL.

Many thanks

SELECT DISTINCT TO_CHAR((“DPS_USER”.REGISTRATION_DATE, ‘Mon-YY’), (“ACTIVITY_LOG”.LOG_TIME, ‘Mon-YY’)), (COUNTFROM XXXX.ACTIVITY_LOG “ACTIVITY_LOG”(XXXX.IN_USER “IN_USER” INNER JOIN XXXX.DPS_USER "DPS_USER"ON (“IN_USEREXT”.ID = “DPS_USER”.ID)AND to_date (‘01/04/2009’, ‘dd/mm/yyyy’))

2

I see inserting cells from Excel doesn’t work!

3

Try this.

SELECT DISTINCT
TO_CHAR (“DPS_USER”.REGISTRATION_DATE, ‘Mon-YY’),
TO_CHAR (“ACTIVITY_LOG”.LOG_TIME, ‘Mon-YY’),
COUNT (“DPS_USER”.LOGIN),
“IN_USEREXT”.CATEGORYNEW
FROM XXXX.ACTIVITY_LOG “ACTIVITY_LOG”
CROSS JOIN
XXXX.ACTIVITY_LOG_URL “ACTIVITY_LOG_URL”
CROSS JOIN
( XXXX.IN_USER “IN_USER”
INNER JOIN
XXXX.DPS_USER “DPS_USER”
ON (“IN_USER”.ID = “DPS_USER”.ID))
INNER JOIN
XXXX.IN_USEREXT “IN_USEREXT”
ON (“IN_USEREXT”.ID = “DPS_USER”.ID)
WHERE ( TRUNC (“DPS_USER”.REGISTRATION_DATE) BETWEEN TO_DATE (‘01/01/2009’,
‘dd/mm/yyyy’)
AND TO_DATE (‘01/04/2009’,
‘dd/mm/yyyy’))
AND “ACTIVITY_LOG”.ACTION IN (4, 5)
OR (“ACTIVITY_LOG”.ACTION = 0
AND (“ACTIVITY_LOG_URL”.FILENAME LIKE
‘%lm_transcript.php%’
OR “ACTIVITY_LOG_URL”.FILENAME LIKE ‘%transcript.php%’))
GROUP BY TO_CHAR (“DPS_USER”.REGISTRATION_DATE, ‘Mon-YY’),
TO_CHAR (“ACTIVITY_LOG”.LOG_TIME, ‘Mon-YY’),
“IN_USEREXT”.CATEGORYNEW
ORDER BY “DPS_USER”.REGISTRATION_DATE DESC

Debbie

4

Could you provide more details on what steps you are taking?

Debbie