ORA-00907 Missing Right Parenthesis (Oracle)

Question

I was previously holding my data in SharePoint. At that time, the below query ran fine :-

SELECT Nz(Abs(Sum(sales_route="Sales Mailbox")),0) AS AcceptDirect
FROM tblQuotesNew AS t1;

Now that I have moved my data to Oracle (but still retrieving it via Access), I get the error ORA-00907 Missing Right Parenthesis.

Can anyone suggest how I can modify the code above that that it is acceptable to Oracle?

Thanks in advance

Answer 1

I think your query counts number of rows with sales_route as 'Sales Mailbox' which can be simply written as:

select count(*) as AcceptDirect
from tblQuotesNew
where sales_route = 'Sales Mailbox';

If you want counts for different routes in the same query, you can do something like this:

select count(case when sales_route = 'Sales Mailbox' then 1 end) as AcceptDirect,
    count(case when sales_route = 'XYZ' then 1 end) as XYZ
from tblQuotesNew
where sales_route in ('Sales Mailbox', 'XYZ');