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.

Nick Krasnov · Accepted Answer · 2012-09-23 15:34:00Z

2

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.

edited Sep 23, 2012 at 15:34

answered Sep 23, 2012 at 15:26

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user964147 Over a year ago

thank you very much for your Answer, when i dont supply ba.uuid then oracle saying "No GROUP BY expression".

Nick Krasnov Over a year ago

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

BellevueBob · Accepted Answer · 2012-09-23 16:01:09Z

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.

Collectives™ on Stack Overflow

Error when executing Oracle query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related