Why does my query produce error "ORA-00933: SQL command not properly ended"?

angel picture angel · Apr 18, 2009 · Viewed 11.1k times · Source

My query:

CREATE VIEW cambiodatos AS 
SELECT 
    a.last_name||','||a.first_name AS "Nombre", 
    a.salary AS "Salario", 
    b.name AS "Nombre Departamento", 
    c.name AS "Nombre de Region"
FROM 
    s_emp a, s_dept b, s_region c
WHERE 
    a.dept_id = b.id AND b.region_id = c.id

UPDATE 
     cambiodatos 
SET 
     name = 'North America'
WHERE 
     last_name = 'Biri'||','||first_name = 'Ben'

The error:

ORA-00933: SQL command not properly ended

Answer

Lasse V. Karlsen picture Lasse V. Karlsen · Apr 20, 2009

The exact reason for why you're getting that error is that you have this WHERE-clause:

last_name = 'Biri'||','||first_name = 'Ben'

This is not legal syntax.

This would be:

last_name = 'Biri' AND first_name = 'Ben'

Or something like this:

name = 'Biri'||','||'Ben'

but then you could just write it like this:

name = 'Biri,Ben'

The problem is that it looks to me that you're using the second || there as an AND clause, but that doesn't fit in with the comma you're trying to add.

Perhaps you're trying to execute this?

last_name || ',' || first_name = 'Biri,Ben'

In any case, as others have pointed out, if you fix that syntax problem, you'll just get other error message about missing column names.