CODEWITHSUNDEEP
sqloracle-database
user964147
user964147

Reputation: 739

why oracle is saying invalid character?

I am very new to Oracle, While i am practicing some examples i encountered with the problem "invalid character (SQL-HY000)".

Below are sample table and data that i used for my practice

CREATE TABLE games (
  id INT NOT NULL PRIMARY KEY ,
  city VARCHAR(20),
  name VARCHAR(20)
);     

INSERT INTO games(id,city,name) VALUES (2004,'Athens','football'); 
INSERT INTO games(id,city,name) VALUES (2008,'Beijing','cricket');

SELECT id, COUNT(*) over() as rowcount 
  FROM games g 
 where name='football' 
 GROUP BY CASE 
            WHEN name='football' 
            THEN g.name 
          END;

What is the invalid character in the select statement ?

Any help will be greatly appreciated

Upvotes: 0

Views: 1355

Answers (1)

João Silva
João Silva

Reputation: 91299

Because of the CASE within the GROUP BY. Also, GROUP BY is not required, since you are already filtering the football rows in the WHERE clause:

SELECT id, COUNT(*) OVER() as rowcount 
FROM games g 
WHERE name = 'football'

When you use an analytic function, such as COUNT(*) OVER(), you don't need to use GROUP BY. They serve different purposes.

DEMO.

Upvotes: 2

Related Questions