Merge statement - Mailing list pgsql-admin
| From | Pepe TD Vo |
|---|---|
| Subject | Merge statement |
| Date | |
| Msg-id | 290131249.7032562.1568999216076@mail.yahoo.com Whole thread Raw |
| Responses |
RE: Merge statement
(Igor Neyman <ineyman@perceptron.com>)
|
| List | pgsql-admin |
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
v_ErrorCode bigint;
v_ErrorMsg varchar(512);
v_Module varchar(32) = 'PR_MIG_STG_APPL_CDIM';
begin
----
-- 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,
crtd_user_id, sbmtd_dt_id, mig_filename, mig_modified_dt
FROM stg_application_cdim
ORDER by mig_filename ) stg
ON ( prod.receipt_number = stg.receipt_number )
WHEN MATCHED THEN UPDATE SET
prod.application_id = stg.application_id,
prod.init_frm_id = stg.init_frm_id,
prod.frm_typ_id = stg.frm_typ_id,
prod.init_src_sys_id = stg.init_src_sys_id,
prod.init_svc_ctr_id = stg.init_svc_ctr_id,
prod.crtd_user_id = stg.crtd_user_id,
prod.sbmtd_dt_id = stg.sbmtd_dt_id,
prod.mig_filename = stg.mig_filename,
prod.mig_modified_dt = current_timestamp
WHEN NOT MATCHED THEN INSERT
(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.crtd_user_id,
prod.sbmtd_dt_id,
prod.mig_filename
) SELECT (
stg.Receipt_Number,
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_filename
)
;
----
-- Set the return code to 0
----
v_Ret := SQLCODE;
----
-- Exception error handler
----
exception
when others then
v_ErrorCode := SQLCODE;
v_ErrorMsg := SQLERRM;
v_Ret := v_ErrorCode;
----
-- Commit the record into the ErrorLog
----
pr_write_error_log( sys_context('userenv','session_user'), sys_context('userenv','host'),
v_Module, v_ErrorCode, v_ErrorMsg );
----
-- Intentionally leaving the "commit" to application
----
end;
$$ language plpgsql;
ERROR: "application_cdim" is not a known variable
LINE 13: MERGE INTO APPLICATION_CDIM prod
^
SQL state: 42601
Character: 349
even 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 perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
No one in this world is pure and perfect. If you avoid people for their 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 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
pgsql-admin by date: