Error when executing Oracle query

Question

I am very new to Oracle, i am developing a query to my project. For me it seems to be everything is OK but when executing the query oracle is giving the error "ORA-00905: missing keyword".

Her is my query::

SELECT DISTINCT ba.uuid AS uuid
 , COUNT(*) over() AS rowcount 
FROM basicaddress ba 
WHERE ba.postalcode='143456' 
 OR ba.lastname LIKE '%143456%' 
 OR ba.city LIKE '%143456%'
GROUP BY CASE WHEN ba.postalcode='143456' THEN ba.postalcode, ba.uuid END
 , CASE WHEN ba.lastname LIKE '%143456%' THEN ba.lastname, ba.uuid END
 , CASE WHEN ba.city LIKE '%143456%' THEN ba.city, ba.uuid 
 ELSE ba.postalcode,ba.uuid END 
ORDER BY CASE WHEN ba.postalcode='143456' THEN ba.postalcode END DESC
 , CASE WHEN ba.lastname LIKE '%143456%' THEN ba.lastname END ASC
 , CASE WHEN ba.city LIKE '%143456%' THEN ba.city ASC 
 ELSE ba.postalcode END DESC

What Key word i am missing ? Any help will be greatly appreciated.

Answer 1

The error is in your CASE statements

CASE 
 WHEN ba.postalcode='143456' 
 THEN ba.postalcode, ba.uuid <-- here. You can return value of only one column
END; 

You have to decide between ba.postalcode and ba.uuid. Or, if you want to see values of both columns in your final result set concatenate them using || operator or CONCAT function using implicit or explicit type conversion.

• user964147: thank you very much for your Answer, when i dont supply ba.uuid then oracle saying "No GROUP BY expression".
• Nick Krasnov: As I've noticed, uuid is present in every result of CASE statement in the GROUP BY clause, you can move uuid column in front of the CASE statement: group by uuid, CASE WHEN .... THEN

Answer 2

I suspect you intended to use an ELSE clause in your CASE expression, perhaps like this:

SELECT DISTINCT ba.uuid AS uuid
 , COUNT(*) AS rowcount
FROM basicaddress ba 
WHERE ba.postalcode='143456'
 OR ba.lastname LIKE '%143456%'
 OR ba.city LIKE '%143456%' 
GROUP BY CASE WHEN ba.postalcode='143456' 
 THEN ba.postalcode
 ELSE ba.uuid END
 , CASE WHEN ba.lastname LIKE '%143456%' 
 THEN ba.lastname
 ELSE ba.uuid END
 , CASE WHEN ba.city LIKE '%143456%' 
 THEN ba.city
 ELSE ba.postalcode,ba.uuid END
ORDER BY CASE WHEN ba.postalcode='143456'
 THEN ba.postalcode END DESC
 , CASE WHEN ba.lastname LIKE '%143456%' 
 THEN ba.lastname END ASC
 , CASE WHEN ba.city LIKE '%143456%'
 THEN ba.city ASC
 ELSE ba.postalcode END DESC

You also do not need the over() clause on your COUNT() function. Also, I refomratted your query in a way to hopefully make the syntax easier to follow, and corrected what I thought was a syntax error in one of your CASE expressions. Note that to be safe, your should always provide an ELSE condition. I'll leave that part to you.