Did you get an ORA-00936: missing expression error? Learn what it means and how to resolve it in this article.
ORA-00936 Cause
The error you’ve gotten is this:
ORA-00936: missing expression
Oracle’s official “cause and action” that appears along with the error is:
Cause: A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE. Action: Check the statement syntax and specify the missing component.
So, in summary, the query is missing some clause that it needs in order to run.
ORA-00936 Solution
To resolve the ORA-00936 error:
- Check that your column names are all listed correctly in the SELECT clause
- Ensure you have a FROM clause in your SELECT statement. Even if you aren’t selecting from a table, you still need FROM in Oracle SQL, so you could use the DUAL table (LINK)
- Remove any commas that shouldn’t be in your query
Missing Columns
The ORA-00936 error often occurs when you leave out the columns in the SELECT clause.
For example:
SELECT
FROM students;ORA-00936: missing expression
This is because you need to list the column names after the word SELECT and before the word FROM.
This query should work:
SELECT student_id, first_name, last_name
FROM students;
Missing FROM
This error can also occur if you don’t have a FROM keyword in your SELECT statement.
For example, this query will display an error:
SELECT first_name, last_name
WHERE student_id = 5;There is no FROM clause in this query, so you’ll get an error.
Correct the query to add the FROM clause, so it knows which table to query.
SELECT first_name, last_name
FROM students
WHERE student_id = 5;Remove Commas
Sometimes you have all of the right keywords, but you’re still getting the ORA-00936: missing expression error.
For example this query gives an error:
SELECT first_name, last_name,
FROM students
WHERE student_id = 5;The reason for this is because there is a comma after the final column “last_name”, and then there is the FROM keyword.
Commas should only be used when you want to specify another column or table, and not before a keyword like we have in this example.
To correct it, remove the comma.
SELECT first_name, last_name
FROM students
WHERE student_id = 5;
ORA-00936 in UPDATE Statement
If you’re getting an ORA-00936: missing expression in an UPDATE statement, then the same steps can be taken:
- Check that you have all the keywords that are required (UPDATE, SET)
- Check there are no extra commas where there shouldn’t be.
- If you’re using a subquery inside the UPDATE statement, then ensure that subquery has all the right keywords and no extra commas as mentioned earlier
ORA-00936 in INSERT Statement
Just like the UPDATE statement, you can also get an ORA-00936: missing expression in an INSERT statement.
The same steps can be taken:
- Check that you have all of the required keywords.
- Check there are no extra commas
- Check that the number of values and the number of columns are the same
- If you’re using a subquery inside the INSERT statement, then ensure that subquery has all the right keywords and no extra commas as mentioned earlier
So, that’s how you resolve the ORA-00936 error in your SQL query. If you have any questions on this error, leave a comment below.
Hi Ben,
I see an issue with my Oracle queries, but I don’t know why:
1. This one works
SELECT contact_id as id, CONTACTS.* from CONTACTS WHERE ROWNUM <=1;
2. This one fails, with the error SQL Error: ORA-00936: missing expression
00936. 00000 – "missing expression"
SELECT contact_id as id, * from CONTACTS WHERE ROWNUM <=1;
Hi Xiao, it could be because of the use of *. Perhaps Oracle thinks that if you use * with other columns you need to specify the table name as well.
ORA-00936: missing expression
When using this query
UPDATE RECEIVED_MAILS SET EVENT_NAME = @pEventName WHERE SENDER = @pSender AND RECIPIENT= @pRecipient AND SUBJECT = @pSubject AND MESSAGE = @pMessage.
kindly advise
Hi Oracle
I have a problem with the insert in the beggining of subquery of the values, i just ran the subquery and it works, please help me:
INSERT INTO DSA.TMP_DIM_CLIENTE (
MANDT,
SPRAS,
ORGANIZACION_VENTAS,
CANAL_DISTRIBUCION,
SECTOR,
CODIGO_CLI,
CODIGO,
RUC,
GRUPO,
COD_CONDICION_PAGO,
DIAS_ZDMC,
NOMBRE2,
NOMBRE1,
CALLE1,
TELF1,
COD_PROVINCIA,
POBLACION,
DISTRITO,
COD_CANAL,
CANAL,
COD_SUBCANAL,
SUBCANAL,
COD_SEG,
SEGMENTO,
COD_TIPO_CLI,
TIPO_CLI,
COD_PALLET_ESTIBADORES,
PALLET_ESTIBADORES,
ASESOR,
AGENTE,
COD_ZONA_VENTA,
ZONA_VENTA,
INCO1,
INCO2,
CAPACIDAD,
PRODVENDIDO,
USO_FINAL,
CATEGORIA_CLI,
CATEGORIA_NEG,
EXPORTADOR,
DENSIDAD,
SUB_DIV_INEN,
ZONAS,
CC_ZONA,
SUBZONA,
CREADO_EL,
ETL_TSTAMP,
BLOQUEADO
) VALUES(
SELECT DISTINCT
NVL(dsa.limpiar_cadena(CLI.MANDT),’-‘) MANDT,
NVL(dsa.limpiar_cadena(CLI.SPRAS),’-‘) SPRAS,
NVL(dsa.limpiar_cadena(DCLI.VKORG),’-‘) Organizacion_ventas,
NVL(dsa.limpiar_cadena(dcli.vtweg),’-‘) canal_distribucion,
NVL(dsa.limpiar_cadena(dcli.spart),’-‘) sector,
dsa.limpiar_numero(CLI.KUNNR)CODIGO_CLI ,
NVL(dsa.limpiar_cadena(CLI.KUNNR),’-‘)CODIGO ,
NVL(dsa.limpiar_cadena(cli.STCD1),’POR DEFINIR’) ruc,
NVL(dsa.limpiar_cadena(CLI.KTOKD),’-‘) AS GRUPO,
DCLI.ZTERM,
TVZBT.vtext DIAS_ZDMC,
–pais.butxt,
NVL(dsa.limpiar_cadena(CLI.NAME2),NVL(dsa.limpiar_cadena(CLI.NAME1),’POR DEFINIR’)) NOMBRE2,
CASE NVL(dsa.limpiar_cadena(CLI.KTOKD),’-‘)
WHEN ‘ZTER’ THEN — SOLO DE DESTINATARIOS
CASE NVL(dsa.limpiar_cadena(CLI.NAME2),’-‘)
WHEN ‘-‘ THEN NVL(dsa.limpiar_cadena(CLI.NAME1),’POR DEFINIR’)
ELSE CASE NVL(dsa.limpiar_cadena(CLI.NAME1),’-‘)
WHEN ‘-‘ THEN NVL(dsa.limpiar_cadena(CLI.NAME2),’POR DEFINIR’)
ELSE CASE dsa.limpiar_cadena(CLI.NAME1) WHEN dsa.limpiar_cadena(CLI.NAME2) THEN dsa.limpiar_cadena(CLI.NAME1)
ELSE CONCAT(CONCAT(dsa.limpiar_cadena(CLI.NAME1),’ ‘),dsa.limpiar_cadena(CLI.NAME2)) END
END
END
ELSE
NVL(dsa.limpiar_cadena(CLI.NAME1),’POR DEFINIR’)
END AS NOMBRE1, –28/10/2020 CAMPO ACTUALIZADO PARA OBTENER NOMBRE COMPLETO
–NVL(dsa.limpiar_cadena(CLI.NAME1),’POR DEFINIR’) NOMBRE1,
NVL(dsa.limpiar_cadena(CLI.STRAS ),’POR DEFINIR’)CALLE1,
telf1,
cli.regio as cod_provincia,
NVL(dsa.limpiar_cadena(CLI.MCOD3),’POR DEFINIR’) AS POBLACION,
NVL(dsa.limpiar_cadena(CLI.ORT02),NVL(dsa.limpiar_cadena(CLI.MCOD3),’POR DEFINIR’)) AS DISTRITO,
NVL(dsa.limpiar_cadena(DCLI.KDGRP),’POR DEFINIR’) AS COD_CANAL,
NVL(dsa.limpiar_cadena(d.ktext),’POR DEFINIR’) CANAL,
NVL(dsa.limpiar_cadena(DCLI.kvgr1),’POR DEFINIR’) AS COD_subcanal ,
NVL(dsa.limpiar_cadena(g1.bezei),’POR DEFINIR’) subcanal,
NVL(dsa.limpiar_cadena(DCLI.kvgr2),’POR DEFINIR’) AS cod_seg,
NVL(dsa.limpiar_cadena(g2.bezei),’POR DEFINIR’) segmento,
NVL(dsa.limpiar_cadena(DCLI.kvgr3),’POR DEFINIR’) AS COD_tipo_cli,
NVL(dsa.limpiar_cadena(g3.bezei ),’POR DEFINIR’) tipo_cli,
NVL(dsa.limpiar_cadena(DCLI.kvgr4),’POR DEFINIR’) AS COD_pallet_estibadores,
NVL(dsa.limpiar_cadena(g4.bezei),’POR DEFINIR’) pallet_estibadores,
NVL(dsa.limpiar_cadena(asesor.asesor),’POR DEFINIR’) asesor,
NVL(dsa.limpiar_cadena(agente.agente),’POR DEFINIR’) agente,
DCLI.BZIRK COD_ZONA_VENTA,
C.BZTXT ZONA_VENTA,
DCLI.INCO1,
DCLI.INCO2,
NVL(dsa.limpiar_cadena(Capacidad.Capacidad),’POR DEFINIR’) Capacidad,
NVL(dsa.limpiar_cadena(prodVendido.prodVendido),’POR DEFINIR’) prodVendido,
NVL(dsa.limpiar_cadena(usoFinal.usoFinal),’POR DEFINIR’) uso_final,
NVL(dsa.limpiar_cadena(catCli.catCli),’POR DEFINIR’) categoria_Cli,
NVL(dsa.limpiar_cadena(catNeg.catNeg),’POR DEFINIR’) categoria_Neg,
NVL(dsa.limpiar_cadena(exportador.exportador),’POR DEFINIR’) exportador,
NVL(dsa.limpiar_cadena(DENSIDAD.DENSIDAD),’POR DEFINIR’) DENSIDAD,
NVL(dsa.limpiar_cadena(subdivInen.subdivInen),’POR DEFINIR’) sub_div_Inen,
NVL(dsa.limpiar_cadena(ZONAS.ZONAS),’POR DEFINIR’) ZONAS,
NVL(dsa.limpiar_cadena(CCZONAS.CCZONA),’POR DEFINIR’) CC_ZONA,
NVL(dsa.limpiar_cadena(SUBZONAS.SUBZONA),’POR DEFINIR’) SUBZONA,
–,interl.kunnr as cod_padre,interl.parvw as cod_tipo_interloc
to_date(CLI.ERDAT, ‘yyyymmdd’) CREADO_EL,
SYSDATE ETL_TSTAMP,
CASE
WHEN NVL(dsa.limpiar_cadena(DCLI.AUFSD),’-‘) = ‘-‘ THEN ‘ACTIVO’
ELSE ‘INACTIVO’ END BLOQUEADO
FROM dsa.KNA1 CLI
INNER JOIN dsa.KNVV DCLI ON DCLI.KUNNR = CLI.KUNNR and cli.mandt = dcli.mandt
LEFT JOIN dsa.TVZBT TVZBT ON TVZBT.MANDT =DCLI.MANDT AND TVZBT.zterm=DCLI.zterm AND TVZBT.spras = cli.spras
LEFT JOIN dsa.T171T C ON C.MANDT =CLI.MANDT AND C.BZIRK =DCLI.BZIRK AND cli.spras = C.spras — ZONA CIO (ZONA DE VENTA)
LEFT JOIN dsa.T151T D ON D.KDGRP = DCLI.KDGRP AND D.MANDT = CLI.MANDT AND cli.spras = D.spras — CANAL
LEFT join dsa.TVV1t g1 on g1.kvgr1=DCLI.kvgr1 and g1.mandt = CLI.mandt and cli.spras = g1.spras — SUB CANAL
LEFT join dsa.TVV2t g2 on g2.kvgr2=DCLI.kvgr2 and g2.mandt = CLI.mandt and cli.spras = g2.spras — SEGMENTO
LEFT join dsa.TVV3t g3 on g3.kvgr3=DCLI.kvgr3 and g3.mandt = CLI.mandt and cli.spras = g3.spras — TIPO CLIENTE
LEFT join dsa.TVV4t g4 on g4.kvgr4=DCLI.kvgr4 and g4.mandt = CLI.mandt and cli.spras = g4.spras — PALLET_ESTIBADOR
LEFT JOIN dsa.T005U PROV ON cli.LAND1 = PROV.LAND1 AND cli.MANDT = PROV.MANDT AND prov.BLAND = cli.REGIO
–inner join dsa.KNVP interl on interl.kunn2= cli.kunnr and interl.mandt = CLI.mandt
LEFT JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as asesor FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’02’ — ASESOR COMERCIAL
)asesor on asesor.mandt = DCLI.mandt and asesor.KUNNR = cli.KUNNR
LEFT JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as agente FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’01’ — AGENTES DE SERVICIO
)agente on agente.mandt = CLI.mandt and agente.KUNNR = cli.KUNNR
LEFT JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as Capacidad FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’03’ — CAPACIDAD BODEGA
)Capacidad on Capacidad.mandt = CLI.mandt and Capacidad.KUNNR = cli.KUNNR
LEFT JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as prodVendido FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’04’ — PRODUCTO VENDIDO
)prodVendido on prodVendido.mandt = CLI.mandt and prodVendido.KUNNR = cli.KUNNR
LEFT JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as usoFinal FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’05’ — USO FINAL CEMENTO
)usoFinal on usoFinal.mandt = CLI.mandt and usoFinal.KUNNR = cli.KUNNR
LEFT JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as catCli FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’06’ — CATEGORIA CLIENTE
)catCli on catCli.mandt = CLI.mandt and catCli.KUNNR = cli.KUNNR
left JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as catNeg FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’07’ — CATEGORIA DE NEGOCIO
)catNeg on catNeg.mandt = CLI.mandt and catNeg.KUNNR = cli.KUNNR
LEFT JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as exportador FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’08’ — EXPORTADOR
)exportador on exportador.mandt = CLI.mandt and exportador.KUNNR = cli.KUNNR
LEFT JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as DENSIDAD FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’09’ — DENSIDAD
)DENSIDAD on DENSIDAD.mandt = CLI.mandt and DENSIDAD.KUNNR = cli.KUNNR
LEFT JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as subdivInen FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’10’ — SUBDIVISIÓN INEN
)subdivInen on subdivInen.mandt = CLI.mandt and subdivInen.KUNNR = cli.KUNNR
LEFT JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as ZONAS FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’11’ — ZONAS
)ZONAS on ZONAS.mandt = CLI.mandt and ZONAS.KUNNR = cli.KUNNR
LEFT JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as CCZONA FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’27’ — CC ZONAS
)CCZONAS on CCZONAS.mandt = CLI.mandt and CCZONAS.KUNNR = cli.KUNNR
LEFT JOIN (
SELECT cli.mandt, cli.kunnr, cli.cod_seg, cli.cod_opc, opc.descripcion as SUBZONA FROM dsa.ZTBSD_CLIENTES CLI
INNER JOIN dsa.ZTBSD_OPCION_SEG OPC ON OPC.COD_SEG = CLI.COD_SEG AND OPC.COD_OPC = CLI.COD_OPC AND OPC.MANDT = CLI.MANDT
WHERE OPC.COD_SEG = ’28’ — SUBZONAS
)SUBZONAS on SUBZONAS.mandt = CLI.mandt and SUBZONAS.KUNNR = cli.KUNNR
–INNER JOIN dsa.T001 pais ON pais.bukrs = DCLI.vkorg and pais.MANDT =CLI.MANDT
WHERE CLI.LAND1= ‘EC’ and CLI.spras = ‘S’);