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
        )
;

--
Angular momentum makes the world go 'round.