ORA-00936: missing expression for a '='

Question

A particular question that I've been asked to do for my D.A.D class is as follows:

• Write a SQL statement that does the following: • Display the StuId, Movie No, Title, Runtime, Rating code, Rating Short Description, tmdb score for movies that meet any of these criteria: Rating code of M plus a runtime between 160-165 (inclusive) Rating code of G plus a runtime less than 90 Rating code of PG plus a runtime either 120 or 121 Rating code of MA plus a runtime 185 minutes or more • The query must also only include movies that have a tmdb_score more than 6.1. • The list must be in Ascending movie no sequence.

so in response, I've written up this:

SELECT '103040698' as StudID, M.MovieNo, M.Title, M.RunTime, M.RatingCode, M.TMDB_Score, R.SHORTDESC, C.COLOURNAME
FROM Movie0698 M
INNER JOIN RATING0698 R
ON M.RatingCode = R.RatingCode
INNER JOIN COLOURTYPE0698 C
ON M.COLOURCODE = C.COLOURCODE
WHERE (M.RatingCode = 'M' AND M.RunTime BETWEEN 160 AND 165 AND M.TMDB_Score > 6.1) 
OR (M.RatingCode = 'G' AND M.RunTime = < 90 AND M.TMDB_Score > 6.1) 
OR (M.RatingCode = 'PG' AND M.RunTime BETWEEN 120 AND 121 AND M.TMDB_Score > 6.1)
OR M.RatingCode = 'MA' AND M.RunTime BETWEEN >=185 AND M.TMDB_Score > 6.1)
ORDER BY M.MovieNo ASC;

however, the error message

'OR (M.RatingCode = 'G' AND M.RunTime = < 90 AND M.TMDB_Score > 6.1) 
                 
Error at line 8:
ORA-00936: missing expression'

keeps on coming up. with the online program (SQLjunior) pointing out that the '=' is the issue in line 8. I'm unsure where I've gone wrong since I've been using the same formatting for the rest of the question via what's been advised by my tutor and those queries run fine.

`M.RunTime = < 90` should be `M.RunTime <= 90`; `OR M.RatingCode = 'MA'` should be `OR (M.RatingCode = 'MA'` — Dmitry Bychenko, Apr 30 '21 at 08:39
@DmitryBychenko While it is usually helpful to format the SQL nicely; in this case, the OP has included the error message with a line number that corresponds to a line in the code and by changing the formatting you are invalidating that link between line number and the actual code. I've rolled back your edit to keep the line number valid. — MT0, Apr 30 '21 at 08:40

score 1 · Accepted Answer · answered Apr 30 '21 at 08:35

1

The "less than or equal" operator is <=, not >=. I.e., in the condition for M.RunTime, you should have M.RunTime <= 90 instead of M.RunTime =< 90.

answered Apr 30 '21 at 08:35

Mureinik

297,002
52
306
350

sorry thank you for that I should of proofread my query now my Error is on "line 10: ORA-00933: SQL command not properly ended" but I've got an order by right after – Vas Theo Apr 30 '21 at 08:38
@VasTheo seems like you're missing a `(` after the last `OR` in the `where` clause – Mureinik Apr 30 '21 at 08:39

score 1 · Answer 2 · answered Apr 30 '21 at 08:43

You have three typos:

OR (M.RatingCode = 'G' AND M.RunTime = < 90 AND M.TMDB_Score > 6.1)

The = < should be <=.

and in:

OR M.RatingCode = 'MA' AND M.RunTime BETWEEN >=185 AND M.TMDB_Score > 6.1)

You need to remove the BETWEEN.

and:

OR M.RatingCode = 'MA' AND M.RunTime BETWEEN >=185 AND M.TMDB_Score > 6.1)

Is missing the opening ( after the OR.

ORA-00936: missing expression for a '='

2 Answers2