CODEWITHSUNDEEP
sqloracle-database
user964147
user964147

Reputation: 739

Error when executing Oracle query

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.

Upvotes: 1

Views: 124

Answers (2)

BellevueBob
BellevueBob

Reputation: 9618

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.

Upvotes: 0

Nick Krasnov
Nick Krasnov

Reputation: 27251

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.

Upvotes: 2

Related Questions