Re: query can't merge into table of the other schema

On 11/07/2018 09:10 AM, Pepe TD Vo wrote:
[snip]

ERROR:  "cidrdba.sc_date_in" is not a known variable

That doesn't look like a "can't merge table from another schema" error.

LINE 13:    MERGE INTO cidrdba.sc_date_in prod

                       ^

SQL state: 42601

Character: 352

 

What happens when you run the statement through psql?

MERGE INTO cidrdba.sc_date_in prod
USING (
        SELECT Receipt_Number,date_in,
               mig_filename,mig_insert_dt,
               mig_modified_dt
        FROM cidr_staging.STG_Date_In
        ORDER by mig_seq
      ) stg
ON ( prod.receipt_number = stg.receipt_number )
WHEN MATCHED THEN UPDATE SET
--   prod.Receipt_Number     = stg.Receipt_Number,
   prod.Date_In              = stg.Date_In,
   prod.mig_filename         = stg.mig_filename,
   --prod.mig_insert_dt      = stg.mig_insert_dt,
   --prod.mig_modified_dt    = stg.mig_modified_dt
   prod.mig_modified_dt      = sysdate
WHEN NOT MATCHED THEN INSERT
        (
                prod.Receipt_Number,
                prod.Date_In,
                prod.mig_filename,
                prod.mig_insert_dt,
                prod.mig_modified_dt
        ) VALUES (
                stg.Receipt_Number,
                stg.Date_In,
                stg.mig_filename,
                sysdate,
                --stg.mig_insert_dt,
                null
                --stg.mig_modified_dt
        )
;