ORA-00936: missing expression Solution - Database Star

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

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.

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;
Reply
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.
Reply
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
Reply
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,
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
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,
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’);
Reply
Leave a Comment Cancel Reply

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

Type here.. </div></fieldset></div><div class="ast-comment-formwrap ast-row"><p class="comment-form-author ast-grid-common-col ast-width-lg-33 ast-width-md-4 ast-float"><label for="author" class="screen-reader-text">Name*</label><input id="author" name="author" type="text" value="" placeholder="Name*" size="30" aria-required="true"/></p> <p class="comment-form-email ast-grid-common-col ast-width-lg-33 ast-width-md-4 ast-float"><label for="email" class="screen-reader-text">Email*</label><input id="email" name="email" type="text" value="" placeholder="Email*" size="30" aria-required="true"/></p> <p class="comment-form-url ast-grid-common-col ast-width-lg-33 ast-width-md-4 ast-float"><label for="url"><label for="url" class="screen-reader-text">Website</label><input id="url" name="url" type="text" value="" placeholder="Website" size="30"/></label></p></div> <p class="akismet_comment_form_privacy_notice">This site uses Akismet to reduce spam. <a href="https://akismet.com/privacy/" target="_blank" rel="nofollow noopener">Learn how your comment data is processed</a>.</p>