CODEWITHSUNDEEP
sqloracle-database
jeremy
jeremy

Reputation: 15

Can't figure this error out?

Code :

SELECT * FROM Grade   
WHERE grade = ‘MG’ ‘1-9’  
SELECT * StaffNo, Name, DOB, ReportsTo,  
FROM Staff  
ORDER BY DOB DESC;

I keep getting

ORA-00911: invalid character

is this a problem with the code?

Upvotes: 0

Views: 93

Answers (2)

Anjan Biswas
Anjan Biswas

Reputation: 7932

Couple of problems -

1.You either need to do a UNION between the first and second SELECT statement, in which case the columns (datatypes) on both the SELECTs should match. Also, one of the columns should be DOB

SELECT StaffNo, Name, DOB, ReportsTo  --you can do * here if Grade has exactly 4 columns of same datatype as in columns in the select below
  FROM Grade   
WHERE grade IN ('MG', '1', '9') --Check for Missing grade or grade 1 or grade 9
UNION  
SELECT StaffNo, Name, DOB, ReportsTo
  FROM Staff  
ORDER BY DOB DESC;

Or these are two entirely different queries like-

SELECT * FROM Grade   
WHERE grade IN ('MG','1', '9');

SELECT StaffNo, Name, DOB, ReportsTo  --* means all columns so its either * or just the column name specifically. Both can be done, but doesn't make sense
FROM Staff  
ORDER BY DOB DESC;

2.’ is a wrong character in Oracle, it should rather be '.

3.In the second SELECT the column ReportsTo ends with a ,. This will be considered illegal by Oracle.

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1503409

This is the problem, for potentially two reasons:

WHERE grade = ‘MG’ ‘1-9’

Have you actually included curly quotes in your query? If so, that's probably why Oracle is complaining about an invalid character.

However, it's then unclear exactly what you're trying to match. Do you want anything starting with MG and then a character between 1 and 9? If so, you could use:

WHERE grade BETWEEN 'MG1' AND 'MG9'

If that's not what you mean, you need to explain what you're trying to do more carefully - and understand that if a human can't understand your intention, it's even less likely that a SQL parser will...

Upvotes: 1

Related Questions