Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1650
  • Last Modified:

ORA-00905 missing KEYWORD

i am trying execute this query
merge into CLIENTE c
using tmp_Solicitud s
on (c.CODIGOUNICO = s.CodUnico)
when not matched then
insert (
        c.CODIGOUNICO,
        c.RAZONSOCIAL,
        c.NUMERODOCUMENTO,
        c.BANCA,
        c.CODIGOGRUPO,
        c.CLASIFICACIONSBS,
        c.CLASIFICACIONFEVE,
        c.RATINGEMPRESA,
        c.SEGMENTO,
        c.CODIGOEJECUTIVO,
        c.NOMBREEJECUTIVO,
        c.CODIGOTIENDA
)
values(
        s.CodUnico,
        s.RazonSocial,
        s.NumeroDocumento,
        s.CodTipoBanca,
        s.CodGrupo,
        s.CodCalificacionSBS,
        s.CodFeve,
        s.Rating,
        s.CodSegmentacion,
        s.CodEjecutivoNegocios,
        s.DesEjecutivoNegocios,
        s.LineaCredTienda
       );
but i have this erros, missing keyword, view file
missingKEYWORD.jpg
0
enrique_aeo
Asked:
enrique_aeo
  • 8
  • 6
  • 2
  • +1
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need to specify the WHEN MATCHED part also
0
 
Alex MatzingerCommented:
I believe you need the

WHEN MATCHED THEN <update_clause>

but i am not positive it is required
0
 
enrique_aeoAuthor Commented:
when this happens
 WHEN MATCHED THEN
 must do nothing
 as would the code please
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Alex MatzingerCommented:
merge into CLIENTE c
using tmp_Solicitud s
on (c.CODIGOUNICO = s.CodUnico)
WHEN MATCHED THEN ( )
when not matched then
insert (
0
 
slightwv (䄆 Netminder) Commented:
'when matched' is NOT required.

I ran the following code using 10.2.0.3.

drop table tab1 purge;
create table tab1(col1 char(1));

drop table tab2 purge;
create table tab2(col1 char(1));

insert into tab1 values('a');
insert into tab2 values('b');

merge into tab2
using tab1 on (tab1.col1=tab2.col1)
when not matched then
	insert(col1) values('z')
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
What is your database version?
0
 
enrique_aeoAuthor Commented:
I run that code for the version of the database?
0
 
enrique_aeoAuthor Commented:
I AM USING pl/sql developer
plsqlDEVELOPER.jpg
0
 
slightwv (䄆 Netminder) Commented:
>>I AM USING pl/sql developer

Can you try the code you have from sqlplus?
0
 
enrique_aeoAuthor Commented:
THE SAME ERROR from sql plus
sqlPLUS.jpg
0
 
slightwv (䄆 Netminder) Commented:
You are using a very early release of 9i.  In 10g "when matched" is not required.  It is in 9i.

As the others have said, you must have a WHEN MATCHED.

Try this
merge into CLIENTE c
using tmp_Solicitud s
on (c.CODIGOUNICO = s.CodUnico) 
when not matched then
insert (
        c.CODIGOUNICO,
        c.RAZONSOCIAL,
        c.NUMERODOCUMENTO,
        c.BANCA,
        c.CODIGOGRUPO,
        c.CLASIFICACIONSBS,
        c.CLASIFICACIONFEVE,
        c.RATINGEMPRESA,
        c.SEGMENTO,
        c.CODIGOEJECUTIVO,
        c.NOMBREEJECUTIVO,
        c.CODIGOTIENDA
)
values(
        s.CodUnico,
        s.RazonSocial,
        s.NumeroDocumento,
        s.CodTipoBanca,
        s.CodGrupo,
        s.CodCalificacionSBS,
        s.CodFeve,
        s.Rating,
        s.CodSegmentacion,
        s.CodEjecutivoNegocios,
        s.DesEjecutivoNegocios,
        s.LineaCredTienda
       )
when matched
       update set c.BANCA=c.BANCA;  -- pick a small insignificant column.

Open in new window

0
 
enrique_aeoAuthor Commented:
add the sentence
WHEN MATCHED THEN
(
UPDATE
SET   c.RATINGEMPRESA = s.Rating
)
but the error it is the same
plsqlDEVELOPER.jpg
0
 
enrique_aeoAuthor Commented:
the same error
sqlPLUS.jpg
0
 
enrique_aeoAuthor Commented:
i add
when matched then
but i other error, view file
sqlPLUS.jpg
0
 
enrique_aeoAuthor Commented:
after executing the statement merge lose connection, do not understand why
0
 
slightwv (䄆 Netminder) Commented:
9.2.0.1 was pretty buggy.  I suggest you use one of the other methods from your previos question and forget about merge.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would not use MERGE when all you want to do is INSERT ..
INSERT into CLIENTE 
 ( <column list ...> )
SELECT <column list>
  FROM tmp_Solicitud s
 WHERE NOT EXISTS( SELECT NULL FROM CLIENTE c WHERE c.CODIGOUNICO = s.CodUnico)

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I still prefer the 'minus' method I posted in your other question:
http://www.experts-exchange.com/Database/Oracle/Q_26826045.html?#a34909809
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now