right parenthesis missing ora-00907

Question

SELECT * FROM (
    SELECT
    ORG_ROADMARK,
    COUNT(DISTINCT EQUIP_INITIAL||EQUIP_NUM||move_dtm) AS Billing_Count
    FROM CMD_BILLING_INFO
    WHERE move_dtm BETWEEN' 01-FEB-12' AND '29-FEB-12'
    AND (
        (MOVE_TYPE_CD ='ICR' AND EQUIP_STATUS_CD IN ('L','W'))
        OR
        ( MOVE_TYPE_CD ='RLO' AND EQUIP_STATUS_CD ='L' )
        OR
        ( MOVE_TYPE_CD ='RMT' AND EQUIP_STATUS_CD ='W' )
    ) GROUPBY ORG_ROADMARK
) ORDERBY ORG_ROADMARK

I am getting missing right parenthesis error for the above sql. Using this sql I was able to get the result for the month period and tried to modify this to get result for last 24 months month by month any suggestions please.

Answer 1

There are two causes for the ORA-00907 exception.

The first is, banally, there is a left paranthesis - ( - without a partnering right parenthesis - ). These can be hard to diagnose by hand, especially in a large SQL statement, but are easy enough if you have a decent IDE with a bracket matching function.

The second cause is a syntax error in a SQL statement which contains brackets. If we mistype a keyword Oracle treats it as an object name. This can cause it to throw a number of errors, such as ORA-00905, ORA-00936 and many others in the 00900 to 01499 range. ORA-00907 is one of those. Again, a decent IDE will help here: syntax highlighting can help us to identify typos, by failing to highlight keywords which we have misspelled.

In your particular case it seems likely that your compression of GROUP BY into a single word is likely to be the culprit. You will also need to fix ORDER BY.

Answer 2

the problem seem to be misplaced quote BETWEEN'

BETWEEN' 01-FEB-12' AND '29-FEB-12'

Answer 3

1. You need a space between the words GROUP and BY in your GROUP BY clause and the words ORDER and BY in your ORDER BY clause.

2. Although it won't cause a syntax error, you almost certainly want don't the single quote right next to the BETWEEN. I would strongly suggest that you also not rely on implicit conversions since someone with different NLS settings may want to run this code.

   WHERE move_dtm BETWEEN to_date( '01-FEB-2012', 'DD-MON-YYYY' ) 
                  AND to_date( '29-FEB-2012', 'DD-MON-YYYY' )
   

   or

   WHERE move_dtm BETWEEN date '2012-02-01' AND date '2012-02-29'