FB

ORA-00936: missing expression Solution

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:

  1. Check that your column names are all listed correctly in the SELECT clause
  2. 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)
  3. 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:

  1. Check that you have all the keywords that are required (UPDATE, SET)
  2. Check there are no extra commas where there shouldn’t be.
  3. 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:

  1. Check that you have all of the required keywords.
  2. Check there are no extra commas
  3. Check that the number of values and the number of columns are the same
  4. 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.

4 thoughts on “ORA-00936: missing expression Solution”

  1. 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;

    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.

    2. 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

  2. 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’);

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.