'NOT LIKE' in an SQL query

sql oracle ora-00936
Ariod picture Ariod · Nov 19, 2009 · Viewed 294.3k times · Source

Why does this simple query return 'ORA-00936: missing expression' (the database is Oracle as you can tell):

SELECT * FROM transactions WHERE id NOT LIKE '1%' AND NOT LIKE '2%'

I feel silly, but what am I doing wrong?

Answer

mikej picture mikej · Nov 19, 2009

You have missed out the field name id in the second NOT LIKE. Try:

SELECT * FROM transactions WHERE id NOT LIKE '1%' AND id NOT LIKE '2%'

The AND in the where clause joins 2 full condition expressions such as id NOT LIKE '1%' and can't be used to list multiple values that the id is 'not like'.

Related questions

Syntax error: missing expression (ORA-00936)

I have 2 tables, Facilities and Services. CREATE TABLE Facilities ( facility_id NUMBER(2) NOT NULL, facility_name VARCHAR2(20) NOT NULL, CONSTRAINT pk_facil_id PRIMARY KEY (facility_id) ); CREATE TABLE Services ( service_id NUMBER(2) NOT NULL, service_name VARCHAR(20) NOT NULL, …

Read More
What could cause an ORA-00936 - Missing Expression with the following sql?

We're seeing the error message ORA-00936 Missing Expression for the following SQL: Note that this is just a cut-down version of a much bigger SQL so rewriting it to a inner join or similar is not really in the scope …

Read More
Getting error ORA-00936: missing expression

When I trying the below SQL I am getting the error ORA-00936: missing expression. Please help me on this, I want distinct on those to columns in Oracle SQL SELECT rr.RPT_QUE_I, DISTINCT (rr.ed_sbmt_m, rr.…

Read More