How to write a case statement with multiple joins

Question

How to write the following query:

-- Using a case statement where store location between 1 - 10 is from midtown and the rest being downtown, find the 
--    # of orders that were cancelled that has a product id greater than 2.

There are three tables :

CO.ORDERS

CO.PRODUCTS

CO.ORDER_ITEMS

and my answer is:

SELECT 

CASE WHEN STORE_ID BETWEEN '1' and '10' THEN 'Midtown' ELSE 'DOWNTOWN' END "STORE LOCATION" ,

count(distinct CO.ORDERS.ORDER_ID) AS "Order Count"

FROM  CO.ORDERS

INNER JOIN CO.ORDER_ITEMS

ON CO.ORDERS.ORDER_ID = CO.ORDER_ITEMS.ORDER_ID

INNER JOIN CO.STORES

ON CO.ORDERS.STORE_ID= CO.STORES.STORE_ID

WHERE  PRODUCT_ID >'2'

and ORDER_STATUS = 'CANCELLED'

group by CASE WHEN STORE_ID BETWEEN '1' and '10' THEN 'Midtown' ELSE 'DOWNTOWN' END;

But its showing ORA-00911: invalid character. why?

Answer 1

But its showing ORA-00911: invalid character. why?

So you are using Oracle, but this is a forum for Microsoft products, and the tag you used is for Microsoft SQL Server.

What I can say is that the query you posted parses in SQL Server 2022 (and then fails on the missing table). But apparently, there is something which Oracle does not like. I can't see what that may be, because the query looks very ANSI-compliant to me.

Anyway, you need to find an Oracle forum to ask your question.