Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iBMN9-0001Uj-KV for pgsql-admin@arkaria.postgresql.org; Fri, 20 Sep 2019 17:07:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iBMN7-0005Ne-Ld for pgsql-admin@arkaria.postgresql.org; Fri, 20 Sep 2019 17:07:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iBMN6-0005NO-S1 for pgsql-admin@lists.postgresql.org; Fri, 20 Sep 2019 17:07:05 +0000 Received: from sonic307-2.consmr.mail.bf2.yahoo.com ([74.6.134.41]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iBMN3-00048N-HL for pgsql-admin@lists.postgresql.org; Fri, 20 Sep 2019 17:07:03 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1568999219; bh=BzUb+MrYYPPTIhM3Rb62gV7+WFbfo26IXRGTIN2izpI=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=Jj4ze/07uQItl4sUpgSgqixJdVFIe1hiOTjf1rvB3XyD6Slc8GEWp1ABOcHkXcvPi6I/CWN0cykAMNqtH+qWteOLTpCDusR3VW04eFUM5fmlBO7BKTZu7UyS8tCM/Q+v5c6PCuxqdXgn6og6hKNiyfTgfXuQhVhX2x97IzKG/iAoDe783eXz/bOgg9AV79qlOCqUMwhv2iiH5nEWeylIVcITSdRzQ0ZUT2L3XLTk9kLDOSCxmYrHr6AQ867uow//cbt3Dlo9fpfJE+xtgfs3uGKbeAlgA2VuVgiTHfks/GVEChaUH0xR6ZBKef/FpSpyGRKn6XGOz54zuYJlo4QPYw== X-YMail-OSG: dEDbex4VM1lDHQ0_coP7z_vCEWK_MOF3J4IpFedqEZeLyK8K3KmGg.TgXnB46nC wgD22OInulgBZ5lWTuXNDttbJWNKarrQhgntUrkrbslN66eDFqSImBhfc7Obbzu_EYYj3t7tGvqR i7am8TmZ1kPuP1ld7qT3PuV8DI4rCURF3TYwmhrvZG_JbYM_V89MJn6s63P8obbkBKgkJ939Dqg2 oZ4MkDhcDpMQ6ZlEypFV2iwlIeYQ5cJpwpzzWZzJeEyVlhDd8WH_pScvQ03do01H8iAGroayrRS8 vEtJmD3e3553gaEEUxaEqctCSPXd1xd2KNusdyZfycp0keupB6akJpKToFOFGC1kS4clp0kfvQRr loW1fOuWzjHlaFtJgOKoMCB9CgAY8msu7saLFfKcyJJ6Lg.U7GISjYX67w2e1hrSzXm1fwk6wHSm RvoiD5y97XJbuuThvn6QjlUeNYyrF0y6rdI5qEu7rlFCAkhq.ey3s1WEIboWBs_0jviTwTaXy16z Oo7UxLrsyYcG3nDCnwYL9dAOzGDnz.6YXY78zFHyjSH3zgNuHZup0G1YX46cRCKquOWhVnZizx9k raQnUipOGG.9fglXgndSlb27YeJwzEhbIyFycsDKFLyRCNoPasXD.cChYoEeYI621kxTa36FXkKw .fK7rVNIIfA6t20VObWbFIipj4Jyt7y.fCWOZOZlWq1qH7yQEFG1iAn9LVY8j6.IMYj1RuB9Qm3g 17TmZL9MD_Jj_BjmYI_CKbqfrxq74w.HrJBfJMUW3TaWU2iSoudG8wI.ikWa5.E0urCUj9cLUZS_ 0T3SFhm.a5D5e2FfFxL_9pSNbP0x2reN9LKXofXVZF3Clwgvy1COKN_naxVJ_snH6V.gZVzS5nxE Srdf_hLAvrr66tfTa7NKboaB0n_9nyXplk.tJ9o5nY1Wu.J0wnwX.2aaxMx0NG4YEVkbUeWWQ5QB Ym5jjK_ef6SG0Bc76EJ1n798euefXomlZ3CwAJUiXf2nkQ.U1wZ3jg_5_4mr74TfF5Hji9A2L0PH hLS4yqlVOgWQSP5RljSvANXQ7CHLasefCHa1vnq6FLUwgdFjmydo8J73SwEbXBWH7WHjxIW22Dmx F0DZWqaQSw7yDdnuiqaBdn0PqY.xdO1av_pSoF_KtdAKL2mx7RPmiMtI6YuMDh5yCX79x_JafJuJ aUlQIK9Tv8jRdiKOM_XGdAXyTZ9_ISOOQErDGZRB9KBTZe3dyuMjfXx7FviHCdbW4rXLwzxKU5PM GYzR_hR41wKzrrvH.ZzAXuuFhVoRH7FBdvsMmFFAjhf2FpCPnUDsJdFCRgLsi1GZI Received: from sonic.gate.mail.ne1.yahoo.com by sonic307.consmr.mail.bf2.yahoo.com with HTTP; Fri, 20 Sep 2019 17:06:59 +0000 Date: Fri, 20 Sep 2019 17:06:56 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Pgsql-admin , Pgsql-admin Message-ID: <290131249.7032562.1568999216076@mail.yahoo.com> Subject: Merge statement MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7032561_394954844.1568999216073" References: <290131249.7032562.1568999216076.ref@mail.yahoo.com> X-Mailer: WebService/1.1.14303 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.132 Safari/537.36 Content-Length: 14062 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_7032561_394954844.1568999216073 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello Experts, I have a script to do merging if the two tables' information are match and = if not then do the update.=C2=A0 I don't know what I did wrong, would you p= lease help out? create or replace FUNCTION "ECISDRDM"."PR_MIG_STG_APPL_CDIM" (v_Ret OUT big= int )as $$declare=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ErrorCode=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0bigint;=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_Err= orMsg=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 varchar(512);=C2=A0 = =C2=A0 =C2=A0 =C2=A0 v_Module=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 varchar(32) =3D 'PR_MIG_STG_APPL_CDIM'; begin =C2=A0 =C2=A0----=C2=A0 =C2=A0-- MERGING: STG_APPLICATION_CDIM into APPLICA= TION_CDIM=C2=A0 =C2=A0----=C2=A0 =C2=A0MERGE INTO "ECISDRDM"."APPLICATION_C= DIM" prod=C2=A0 =C2=A0USING (=C2=A0 =C2=A0SELECT Receipt_Number,application= _id, init_frm_id, frm_typ_id, init_src_sys_id, init_svc_ctr_id, crtd_user_i= d, sbmtd_dt_id, mig_filename, mig_modified_dt=C2=A0 =C2=A0FROM stg_applicat= ion_cdim=C2=A0=C2=A0 =C2=A0ORDER by mig_filename ) stg=C2=A0 =C2=A0ON ( pro= d.receipt_number =3D stg.receipt_number )=C2=A0 =C2=A0WHEN MATCHED THEN UPD= ATE SET=C2=A0 =C2=A0 =C2=A0 prod.application_id=C2=A0 =C2=A0 =C2=A0 =C2=A0= =3D stg.application_id,=C2=A0 =C2=A0 =C2=A0 prod.init_frm_id =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =3D stg.init_frm_id,=C2=A0 =C2=A0 =C2=A0 prod.frm_typ_id =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =3D stg.frm_typ_id,=C2=A0 =C2=A0 =C2=A0 prod.init_= src_sys_id=C2=A0 =C2=A0 =C2=A0 =3D stg.init_src_sys_id,=C2=A0 =C2=A0 =C2=A0= prod.init_svc_ctr_id=C2=A0 =C2=A0 =C2=A0 =3D stg.init_svc_ctr_id,=C2=A0 = =C2=A0 =C2=A0 prod.crtd_user_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D stg.crtd_us= er_id,=C2=A0 =C2=A0 =C2=A0 prod.sbmtd_dt_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D= stg.sbmtd_dt_id,=C2=A0 =C2=A0 =C2=A0 prod.mig_filename=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0=3D stg.mig_filename,=C2=A0 =C2=A0 =C2=A0 prod.mig_modified_d= t=C2=A0 =C2=A0 =C2=A0 =3D current_timestamp=C2=A0 =C2=A0WHEN NOT MATCHED TH= EN INSERT=C2=A0 =C2=A0(prod.Receipt_Number, prod.application_id, prod.init_= frm_id, prod.frm_typ_id, prod.init_src_sys_id, prod.init_svc_ctr_id, prod.c= rtd_user_id, prod.sbmtd_dt_id, prod.mig_filename ) SELECT ( stg.Receipt_Num= ber, stg.application_id, stg.init_frm_id, stg.frm_typ_id, stg.init_src_sys_= id, stg.init_svc_ctr_id, stg.crtd_user_id, stg.sbmtd_dt_id, stg.mig_filenam= e=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0)=C2=A0 =C2=A0; =C2=A0 =C2=A0----=C2=A0 =C2=A0-- Set the return code to 0=C2=A0 =C2=A0---- =C2=A0 =C2=A0v_Ret :=3D SQLCODE; ------ Exception error handler----exception=C2=A0 =C2=A0when others then v_= ErrorCode :=3D SQLCODE; v_ErrorMsg=C2=A0 :=3D SQLERRM; v_Ret=C2=A0 =C2=A0 = =C2=A0 =C2=A0:=3D v_ErrorCode; ---- -- Commit the record into the ErrorLog ---- pr_write_error_log( sys_c= ontext('userenv','session_user'), sys_context('userenv','host'), =C2=A0 =C2= =A0v_Module, v_ErrorCode, v_ErrorMsg ); =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Intentionally leaving the "com= mit" to application=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = ----end;$$ language plpgsql; ERROR:=C2=A0 "application_cdim" is not a known variableLINE 13:=C2=A0 =C2= =A0 MERGE INTO APPLICATION_CDIM prod=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^SQL state: 42601Character: 34= 9 even I take schema_name, ECISDRDRM out, I still get an error: ERROR:=C2=A0 "application_cdim" is not a known variableLINE 13:=C2=A0 =C2= =A0 MERGE INTO APPLICATION_CDIM prod=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^SQL state: 42601Character: 34= 9 thank you for your help. v/r, Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir mistakes you will be alone. So judge less, love and forgive more.To call= him a dog hardly seems to do him justice though in as much as he had four = legs, a tail, and barked, I admit he was, to all outward appearances. But t= o those who knew him well, he was a perfect gentleman (Hermione Gingold) **Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely = rich *** Faithful talent *** Sharing success ------=_Part_7032561_394954844.1568999216073 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello Experts,

I have a script= to do merging if the two tables' information are match and if not then do = the update.  I don't know what I did wrong, would you please help out?=

create or replace FUNCTION "ECISDRDM"."PR_MIG_= STG_APPL_CDIM" (v_Ret OUT bigint )
as $$
declare <= /div>
        v_ErrorCode       = ;      bigint;
        v_Error= Msg              varchar(512);
        v_Module          &nb= sp;     varchar(32) =3D 'PR_MIG_STG_APPL_CDIM';

begin

   ----
  &= nbsp;-- MERGING: STG_APPLICATION_CDIM into APPLICATION_CDIM
 = ;  ----
   MERGE INTO "ECISDRDM"."APPLICATION_CDIM= " prod
   USING (
   SELECT Receipt= _Number,application_id, init_frm_id, frm_typ_id, init_src_sys_id, init_svc_= ctr_id,
=09crtd_use= r_id, sbmtd_dt_id, mig_filename, mig_modified_dt
   FRO= M stg_application_cdim 
   ORDER by mig_filename )= stg
   ON ( prod.receipt_number =3D stg.receipt_number= )
   WHEN MATCHED THEN UPDATE SET
  &nb= sp;   prod.application_id       =3D stg.applicatio= n_id,
      prod.init_frm_id=09        =3D stg.init_frm_id,=
      prod.frm_typ_id=09        =3D stg.frm_typ_id,
<= div>      prod.init_src_sys_id      =3D stg.i= nit_src_sys_id,
      prod.init_svc_ctr_id  &= nbsp;   =3D stg.init_svc_ctr_id,
      prod.c= rtd_user_id=09    &= nbsp;  =09=3D stg.crtd_u= ser_id,
      prod.sbmtd_dt_id=09       =09=3D stg.sbmtd_dt_id,
    =   prod.mig_filename         =3D stg.mig_filen= ame,
      prod.mig_modified_dt     = ; =3D current_timestamp
   WHEN NOT MATCHED THEN INSERT=
   (prod.Receipt_Number,
=09prod.application_id,
=09prod.init_frm_id,
=09prod.frm_typ_id,
=09prod.init_src_sys_id,
=09prod.init_svc_ctr_id,
=09prod.crtd_user_id,
=
=09prod.sbmtd_dt_id,
=09prod.mig_filename
=09) SELECT (
<= div>=09stg.Receipt_Number,
=09stg.application_id= ,
=09stg.init_frm_i= d,
=09stg.frm_typ_i= d,
=09stg.init_src_= sys_id,
=09stg.init= _svc_ctr_id,
=09stg= .crtd_user_id,
=09s= tg.sbmtd_dt_id,
=09= stg.mig_filename
           )
=
   ;

   ----
&n= bsp;  -- Set the return code to 0
   ----

   v_Ret :=3D SQLCODE;

= ----
-- Exception error handler
----
exceptio= n
   when others then
=09v_ErrorCode :=3D SQLCODE;
=09v_ErrorMsg  :=3D SQLERRM;
<= div>=09v_Ret    &nb= sp;  :=3D v_ErrorCode;

=09----
=09-- Commit the record into the ErrorLog
=09----
=09pr_write_error_log( sys_context('userenv','sess= ion_user'), sys_context('userenv','host'),
=09   v_Module, v_ErrorCode, v_ErrorMsg )= ;

             =   ----
              &nb= sp; -- Intentionally leaving the "commit" to application
  &= nbsp;             ----
end;
$$ language plpgsql;


ERROR= :  "application_cdim" is not a known variable
LINE 13: =   MERGE INTO APPLICATION_CDIM prod
      &nb= sp;                ^
SQL = state: 42601
Character: 349


eve= n I take schema_name, ECISDRDRM out, I still get an error:

ERROR:  "application_cdim" is not a known variable
LINE 13:    MERGE INTO APPLICATION_CDIM prod
  =                      = ;^
SQL state: 42601
Character: 349


thank you for your help.

v/r,

Bach-Nga
No one in this world is pure and pe= rfect.  If you avoid people for their mistakes you will be alone. So j= udge less, love and forgive more.
To call h= im a dog hardly seems to do him justice though in as much as he had four le= gs, a tail, and barked, I admit he was, to all outward appearances. But to = those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply *= *Love generously **Care deeply **Speak kindly.
*= ** Genuinely rich *** Faithful talent *** Sharing success
------=_Part_7032561_394954844.1568999216073--