Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKwwn-0008I9-KM for pgsql-admin@arkaria.postgresql.org; Fri, 09 Nov 2018 02:55:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gKwwl-0004dm-QM for pgsql-admin@arkaria.postgresql.org; Fri, 09 Nov 2018 02:54:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKwwl-0004da-Fi for pgsql-admin@lists.postgresql.org; Fri, 09 Nov 2018 02:54:59 +0000 Received: from sonic315-14.consmr.mail.bf2.yahoo.com ([74.6.134.124]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKwwi-0008KC-N8 for pgsql-admin@postgresql.org; Fri, 09 Nov 2018 02:54:59 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1541732094; bh=o2sV3bhamfpwLvMskTz21Tt8SsZohH4H2v29Dwfzvug=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=sA7pQHj/l9AlHmhwwzY95T1ZzN/NGJn/iyoRFN9ZDOS8ozz0Gf685dLxf71gaPxiKMu/6K++irRyMHOMo20TO6iNbu+E2OjoHtY+Ov2tc6610BzCE1ORuk1tsAtDwYKRK1tRSNUEQfOaEFvEPGk+D90teEKfmSzZyjYFy2l7+XqfAk+UDn/SpgfBl1Gra7As/mAsMCO37YXN99zBbLkOK91k5sG43TknZ6exDGxL52MH3w6G7EhJvHBtihPt6Zp2K3AISa0xfSCC3G9bQ04ILc6DKuHGYKrbUNRTmXZI5bVKmXnrt2QxB0H5UVeZNiEifBJNLMtZ0bL53myorkdx3Q== X-YMail-OSG: 0y1qBwUVM1ms.gms2BU9ZXmQYw_.vJ3MnvHBwUYh8wlaf.WZnqPS1MGlQD4g_FQ Nq4iqv2V.wIC.UfD8wsoxssmnTyLOaTacDOqPrClTv5cm1lLaMl2dPidmTHDYZvohSJrLezRL4Ns zb5tB30Qpc4_oSu4XiCkT6drhFEK16JNmV3pRYC0QkECBOO3C58sAID7Y25hTvrrDNiMFCFW8oam jROl19khiiPebxKLyhbhl3yF.Z0MXFOflvAFdeCU.QphBVdH0UYZyP3hWUCbiz7XTJHJ59H5plqT 101uFa6w7UD8s40fZNz0eflk.v_uNyey42JLiK0Y4frQO4fx3jC_eL8XQcqRxGWiPzZ153eEsRSx 8vsvAWbRWgm.deAsEYh0d.CyW_NiZbHcnq3Z42sKTHYIyQ85rY1i9TvbILIK2f47ZSwdAOEnqmde _r.UJD1qdfiJoAlJZTeI8X_GYWfhGMod1xVa39H7Eb3XNERPA5iZJDU9BxfxIfoNQbIUpKD2Oq.x s.ZNco1SEgC2nR4xNURDsv_NAYrNB4ghnibwn6YpdqRmGGJ.mWthKhAwPOeeLzDsFNBl9_.Vq8HL d4_GZAGZQDEVkAgKxPNujI8EBWrgHYri9DAWl1hZiNmwvYVCo8MvKY_QObV5yHRZNcTGNfo7Csck wLLZvoCnDRgJT938I2Qa4v0zT8j9Wxlsz2rHdgYbWPqiYPF9NdfU30kcgl7EL2ZzDwod2ohbDhmb XeIobP63o56.BfTplVy6Gcx_nsgIFu00VRl5ib0Uw0YHIhchCUn0X9xjVIuUZgcbkGi.RVcAfaF6 rJhp61L6gfWhfxU82.kJoArx0L21j_SxJsj3VUS_fHW6Yd46d3Y0E0VLcf2S6IhNq.5vtClOK3b9 XsQ6OAdjA6wbtxzfEm94K.yAvwQXv6c8OD7sJkTB6n70wfVZCBf2jqH_cNDS9MYklKRPmXQUuI7Z OYgjdrk4uBwl7llV7kGm11_8R8baT2P69lK1WU1mVTvVCXbwkQsasql3wbY4p6y32G6AgeJ8eaQ7 e2UDMAg6nSuDVbcVATk8- Received: from sonic.gate.mail.ne1.yahoo.com by sonic315.consmr.mail.bf2.yahoo.com with HTTP; Fri, 9 Nov 2018 02:54:54 +0000 Date: Fri, 9 Nov 2018 02:54:51 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Pgsql-admin Message-ID: <1769600488.1326361.1541732091653@mail.yahoo.com> Subject: %rowtype MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1326360_1926280867.1541732091646" References: <1769600488.1326361.1541732091653.ref@mail.yahoo.com> X-Mailer: WebService/1.1.12710 YahooMailNeo Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.77 Safari/537.36 Content-Length: 39178 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1326360_1926280867.1541732091646 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello, Would you please tell me what I did wrong here?=C2=A0 I couldn't find the r= owtype eliminate with a cursor in multi-tables selected. CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_I864" ( v_Ret OUT int= ) RETURNS integer as $$=C2=A0declare=C2=A0=C2=A0 =C2=A0c1 cursor is=C2=A0 = =C2=A0 =C2=A0 SELECT stg.Service_Center, stg.Receipt_Number, stg.Date_Enter= ed, stg.Sequence_Number,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.SSN, stg.A_Nu= mber, stg.Last_Name, stg.First_Name, stg.Middle_Name, stg.DOB,=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0stg.Street_Mail, stg.City_Mail, stg.State_Mail, stg.Zi= p_Mail, stg.Province_Mail,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.Postal_Code= _Mail, stg.Country_Mail, stg.Street_Res, stg.City_Res, stg.State_Res,=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.Zip_Res, stg.Province_Res, stg.Postal_Code_= Res, stg.Country_Res, stg.US_Citizen,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.= Basis, stg.Accompany, stg.mig_filename,stg.mig_insert_dt,stg.mig_modified_d= t,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0prod.receipt_number as prod_receipt_num= ber, prod.date_entered as prod_date_entered,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0prod.sequence_number as prod_sequence_number=C2=A0 =C2=A0 =C2=A0 FROM ci= dr_staging.stg_i864 stg LEFT OUTER JOIN cidrdba.sc_i864 prod=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0ON coalesce(stg.receipt_number,'NULL') =3D coalesce(pro= d.receipt_number,'NULL')=C2=A0 =C2=A0 =C2=A0 =C2=A0 AND coalesce(stg.date_e= ntered,'NULL') =3D coalesce(prod.date_entered,'NULL')=C2=A0 =C2=A0 =C2=A0 = =C2=A0 AND coalesce(stg.sequence_number,'NULL') =3D coalesce(prod.sequence_= number,'NULL')=C2=A0 =C2=A0 =C2=A0 ORDER by stg.mig_seq;=C2=A0 =C2=A0rec1= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 c1%rowtype;=C2=A0 =C2=A0v_rows=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0int =3D 0;=C2=A0 =C2=A0v_seq=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 int =3D0;=C2=A0 =C2=A0v_ErrorCode=C2=A0 =C2=A0 int;=C2= =A0 =C2=A0v_ErrorMsg=C2=A0 =C2=A0 =C2=A0varchar(512);=C2=A0 =C2=A0v_Module= =C2=A0 =C2=A0 =C2=A0 =C2=A0varchar(32) =3D 'PR_MIG_STG_I864';=C2=A0 =C2=A0v= _DDL=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 varchar(10);=C2=A0 =C2=A0v_Rec_Num= =C2=A0 =C2=A0 =C2=A0 cidr_staging.stg_i864.receipt_number%type;=C2=A0 =C2= =A0v_Dat_Ent=C2=A0 =C2=A0 =C2=A0 cidr_staging.stg_i864.date_entered%type;= =C2=A0 =C2=A0v_Seq_Num=C2=A0 =C2=A0 =C2=A0 cidr_staging.stg_i864.sequence_n= umber%type; begin =C2=A0 =C2=A0v_Ret :=3D 0;=C2=A0 =C2=A0for rec1 in c1 loop=C2=A0 =C2=A0 =C2=A0 --dbms_output.put_line('Processing Receipt_Number = ' || rec1.receipt_number);=C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 -- = If the PROD_RECEIPT_NUMBER is null, then the record does not exist in the P= roduction table.=C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 if rec1.prod_= receipt_number is null then=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_Rec_Num :=3D= rec1.receipt_number;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_Dat_Ent :=3D rec1.= date_entered;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_Seq_Num :=3D rec1.sequence= _number; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0insert into cidrdba.sc_i864 values (=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 rec1.Service_Center, rec1.Receipt_Nu= mber, coalesce(rec1.Date_Entered,''),=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 rec1.Sequence_Number, rec1.ssn, rec1.a_number, rec1.last_name, rec1.fir= st_name,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 rec1.middle_name, rec1.do= b, rec1.street_mail, rec1.city_mail, rec1.state_mail,=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 rec1.zip_mail, rec1.Province_Mail, rec1.Postal_Code_Ma= il, rec1.Country_Mail,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 rec1.Street= _Res, rec1.City_Res, rec1.State_Res, rec1.Zip_Res, rec1.Province_Res,=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 rec1.Postal_Code_Res, rec1.Country_Res,= rec1.US_Citizen, rec1.Basis, rec1.Accompany,=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 rec1.mig_filename, rec1.mig_insert_dt, rec1.mig_modified_dt )= ;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_rows :=3D sql%rowcount;=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0if v_rows !=3D 1 then=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 v_Ret :=3D 1;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PERFORM pr_w= rite_error_log( sys_context('userenv','session_user'),=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 sys_context('userenv','host'), v_Module,=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 0, 'INSERT processed ' || v_rows );=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0end if;=C2=A0 =C2=A0 =C2=A0 elsif coalesce(rec1.prod_recei= pt_number,'NULL')=C2=A0 =3D coalesce(rec1.receipt_number,'NULL') and=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 coalesce(rec1.prod_date_entered,'NULL')= =C2=A0 =C2=A0 =3D coalesce(rec1.date_entered,'NULL') and=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 coalesce(rec1.prod_sequence_number,'NULL') =3D coa= lesce(rec1.sequence_number,'NULL') then=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_= Rec_Num :=3D rec1.receipt_number;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_Dat_En= t :=3D rec1.date_entered;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_Seq_Num :=3D r= ec1.sequence_number;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0update cidrdba.sc_i86= 4 set=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 SSN=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D rec1.SSN,=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 A_Number=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0=3D rec1.A_Number,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 Last_Name=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D rec1.Last= _Name,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 First_Name=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=3D rec1.First_Name,=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 Middle_Name=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D = rec1.Middle_Name,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 DOB=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D rec1.DOB,=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Street_Mail=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =3D rec1.Street_Mail,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 City_Mail=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D rec1.C= ity_Mail,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 State_Mail=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=3D rec1.State_Mail,=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 Zip_Mail=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0=3D rec1.Zip_Mail,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Provi= nce_Mail=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D rec1.Province_Mail,=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Postal_Code_Mail=C2=A0 =C2=A0 =C2=A0 =C2= =A0=3D rec1.Postal_Code_Mail,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Coun= try_Mail=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=3D rec1.Country_Mail,=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Street_Res=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0=3D rec1.Street_Res,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 City_Res=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =3D rec1.City_Res,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 State_Res=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D rec1.State_Res,=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 Zip_Res=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =3D rec1.Zip_Res,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 Province_Res=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=3D rec1.Province_= Res,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Postal_Code_Res=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =3D rec1.Postal_Code_Res,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 Country_Res=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D rec1.Countr= y_Res,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 US_Citizen=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=3D rec1.US_Citizen,=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 Basis=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =3D rec1.Basis,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Accompa= ny=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D rec1.Accompany,=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 mig_filename=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0=3D rec1.mig_filename,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 mig_modified_dt=C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D current_timestamp=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0where=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 coalesce(receipt_number,'NULL')=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=3D co= alesce(rec1.receipt_number,'NULL')=C2=A0and=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 coalesce(date_entered,'NULL')=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0=3D coalesce(rec1.date_entered,'NULL') and=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 coalesce(sequence_number,'NULL')=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =3D coalesce(rec1.sequence_number,'NULL');=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0v_rows :=3D sql%rowcount;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if v_rows != =3D 1 then=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_Ret :=3D 1;=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 pr_write_error_log( sys_context('userenv','= session_user'),=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 sys_context('userenv',= 'host'), v_Module,=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 0, 'UPDATE processe= d ' || v_rows || ', expected 1' );=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0end if;= =C2=A0 =C2=A0 =C2=A0 else=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_Ret :=3D 1;=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0PERFORM pr_write_error_log( sys_context('use= renv','session_user'),=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0sys_context('useren= v','host'), v_Module,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00, 'FAILED to proces= s: ' ||=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0rec1.Receipt_Number || ', ' || rec= 1.Date_Entered || ', ' || rec1.Sequence_Number );=C2=A0 =C2=A0 =C2=A0 end i= f;=C2=A0 =C2=A0 =C2=A0 exit when v_Ret !=3D 0 or SQLCODE !=3D 0;=C2=A0 =C2= =A0end loop; =C2=A0 =C2=A0if v_Ret =3D 0 then=C2=A0 =C2=A0 =C2=A0 v_Ret :=3D SQLCODE;=C2= =A0 =C2=A0end if; exception=C2=A0 =C2=A0when others then=C2=A0 =C2=A0 =C2=A0 v_ErrorCode :=3D= SQLCODE;=C2=A0 =C2=A0 =C2=A0 v_ErrorMsg=C2=A0 :=3D SQLERRM; =C2=A0 =C2=A0 =C2=A0 if inserting then=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_d= dl :=3D 'INSERT';=C2=A0 =C2=A0 =C2=A0 elsif updating then=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0v_ddl :=3D 'UPDATE';=C2=A0 =C2=A0 =C2=A0 else=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0v_ddl :=3D 'UNKNOWN';=C2=A0 =C2=A0 =C2=A0 end if; =C2=A0 =C2=A0 =C2=A0 PERFORM pr_write_error_log( sys_context('userenv','ses= sion_user'),=C2=A0 =C2=A0 =C2=A0 sys_context('userenv','host'), v_Module,= =C2=A0 =C2=A0 =C2=A0 0, v_ddl || ' encountered FALTAL ERROR: (' || v_ErrorC= ode || '): ' || v_ErrorMsg ); =C2=A0 =C2=A0 =C2=A0 --dbms_output.put_line('FATAL ERROR while ' || v_ddl |= | ':=C2=A0 Encountered (' || v_ErrorCode || ') : ' || v_ErrorMsg );=C2=A0 = =C2=A0 =C2=A0 --dbms_output.put_line('FATAL ERROR:=C2=A0 Record ' || nvl(v_= Rec_Num,'null') || '~' || nvl(v_Dat_Ent,'null') || '~' || nvl(v_Seq_Num,'nu= ll') );end;$$ LANGUAGE plpgsql; ERROR:=C2=A0 relation "c1" does not existCONTEXT:=C2=A0 compilation of PL/p= gSQL function "PR_MIG_STG_I864" near line 17SQL state: 42P01=C2=A0=C2=A0I f= ound the %rowtype could declare of the table%rowtype,myrow tablename%ROWTYP= E;=C2=A0but my oracle script had union tables, how can I declare it?=C2=A0 = I have tried declare rec1 stg%rowtype or prod%rowtype and/or the cidr_stagi= ng.stg_i864%rowtype... the error is still same, relation does not exist. Any helps and explanation would be appreciated.=C2=A0=C2=A0 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_1326360_1926280867.1541732091646 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hell= o,

Would you pleas= e tell me what I did wrong here?  I couldn't find the rowtype eliminat= e with a cursor in multi-tables selected.


CREATE OR R= EPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_I864" ( v_Ret OUT int ) RETURNS = integer as $$
 declare 
   c1 cursor is
      SELECT stg.Serv= ice_Center, stg.Receipt_Number, stg.Date_Entered, stg.Sequence_Number,
  &nbs= p;      stg.SSN, stg.A_Number, stg.Last_Name, stg.First_Name= , stg.Middle_Name, stg.DOB,
         stg.Street_Mail, stg= .City_Mail, stg.State_Mail, stg.Zip_Mail, stg.Province_Mail,
     = ;    stg.Postal_Code_Mail, stg.Country_Mail, stg.Street_Res, stg.= City_Res, stg.State_Res,
         stg.Zip_Res, stg.Provin= ce_Res, stg.Postal_Code_Res, stg.Country_Res, stg.US_Citizen,
     = ;    stg.Basis, stg.Accompany, stg.mig_filename,stg.mig_insert_dt= ,stg.mig_modified_dt,
         prod.receipt_number as pro= d_receipt_number, prod.date_entered as prod_date_entered,
      &nb= sp;  prod.sequence_number as prod_sequence_number
      FROM c= idr_staging.stg_i864 stg LEFT OUTER JOIN cidrdba.sc_i864 prod
     = ;    ON coalesce(stg.receipt_number,'NULL') =3D coalesce(prod.rec= eipt_number,'NULL')
        AND coalesce(stg.date_entered,'NUL= L') =3D coalesce(prod.date_entered,'NULL')
        AND coalesc= e(stg.sequence_number,'NULL') =3D coalesce(prod.sequence_number,'NULL')
  &nb= sp;   ORDER by stg.mig_seq;
   rec1        &nbs= p;   c1%rowtype;
   v_rows         int =3D= 0;
&nb= sp;  v_seq          int =3D0;
   v_ErrorCo= de    int;
   v_ErrorMsg     varchar(512);
  &nbs= p;v_Module       varchar(32) =3D 'PR_MIG_STG_I864';
  &nb= sp;v_DDL          varchar(10);
   v_Rec_Num&nbs= p;     cidr_staging.stg_i864.receipt_number%type;
   v_Dat_Ent=       cidr_staging.stg_i864.date_entered%type;
   v_Seq_= Num      cidr_staging.stg_i864.sequence_number%type;

begin

   v_Ret :=3D 0;
   for rec1 in c1
=

loop
      --dbms_outpu= t.put_line('Processing Receipt_Number ' || rec1.receipt_number);
    &nb= sp; ----
      -- If the PROD_RECEIPT_NUMBER is null, then the reco= rd does not exist in the Production table.
      ----
     = ; if rec1.prod_receipt_number is null then
         v_Rec= _Num :=3D rec1.receipt_number;
         v_Dat_Ent :=3D re= c1.date_entered;
         v_Seq_Num :=3D rec1.sequence_nu= mber;
<= br id=3D"yiv5697905106yui_3_16_0_ym19_1_1541710097140_14450">
     = ;    insert into cidrdba.sc_i864 values (
        &n= bsp;   rec1.Service_Center, rec1.Receipt_Number, coalesce(rec1.Date_En= tered,''),
            rec1.Sequence_Number, rec1.ss= n, rec1.a_number, rec1.last_name, rec1.first_name,
        &nb= sp;   rec1.middle_name, rec1.dob, rec1.street_mail, rec1.city_mail, re= c1.state_mail,
            rec1.zip_mail, rec1.Provi= nce_Mail, rec1.Postal_Code_Mail, rec1.Country_Mail,
        &n= bsp;   rec1.Street_Res, rec1.City_Res, rec1.State_Res, rec1.Zip_Res, r= ec1.Province_Res,
            rec1.Postal_Code_Res, = rec1.Country_Res, rec1.US_Citizen, rec1.Basis, rec1.Accompany,
     = ;       rec1.mig_filename, rec1.mig_insert_dt, rec1.mig_modi= fied_dt );
         v_rows :=3D sql%rowcount;
    &nbs= p;    if v_rows !=3D 1 then
            v_= Ret :=3D 1;
            PERFORM pr_write_error_log( = sys_context('userenv','session_user'),
            &= nbsp;                   sys_co= ntext('userenv','host'), v_Module,
             = ;                   0, 'INSERT= processed ' || v_rows );
         end if;
     = ; elsif coalesce(rec1.prod_receipt_number,'NULL')  =3D coalesce(rec1.r= eceipt_number,'NULL') and
            coalesce(rec1.= prod_date_entered,'NULL')    =3D coalesce(rec1.date_entered,'NULL= '
) and=
 =           coalesce(rec1.prod_sequence_number,'NUL= L') =3D coalesce(rec1.sequence_number,'NULL') then
        &nb= sp;v_Rec_Num :=3D rec1.receipt_number;
         v_Dat_Ent= :=3D rec1.date_entered;
         v_Seq_Num :=3D rec1.seq= uence_number;
         update cidrdba.sc_i864 set
    =         SSN            &n= bsp;       =3D rec1.SSN,
            = A_Number               =3D rec1.A_N= umber,
=             Last_Name      &nb= sp;       =3D rec1.Last_Name,
          &n= bsp; First_Name             =3D rec1.Fir= st_Name,
            Middle_Name     =       =3D rec1.Middle_Name,
          &nb= sp; DOB                   = ; =3D rec1.DOB,
            Street_Mail   =         =3D rec1.Street_Mail,
        &nb= sp;   City_Mail              =3D re= c1.City_Mail,
            State_Mail    &n= bsp;        =3D rec1.State_Mail,
        &= nbsp;   Zip_Mail               = ;=3D rec1.Zip_Mail,
            Province_Mail  =         =3D rec1.Province_Mail,
        &n= bsp;   Postal_Code_Mail       =3D rec1.Postal_Code= _Mail,
=             Country_Mail      =      =3D rec1.Country_Mail,
          &nbs= p; Street_Res             =3D rec1.Stree= t_Res,
=             City_Res      &nbs= p;        =3D rec1.City_Res,
         = ;   State_Res              =3D rec1= .State_Res,
            Zip_Res      =           =3D rec1.Zip_Res,
        &= nbsp;   Province_Res           =3D rec1.= Province_Res,
            Postal_Code_Res  &nbs= p;     =3D rec1.Postal_Code_Res,
          &nbs= p; Country_Res            =3D rec1.Country_Re= s,
&nbs= p;           US_Citizen       =      =3D rec1.US_Citizen,
           = ; Basis                  =3D r= ec1.Basis,
            Accompany     =         =3D rec1.Accompany,
         = ;   mig_filename           =3D rec1.mig_= filename,
            mig_modified_dt    &= nbsp;   =3D current_timestamp
         where
    =         coalesce(receipt_number,'NULL')    &n= bsp;    =3D coalesce(rec1.receipt_number,'NULL')
 and
     = ;       coalesce(date_entered,'NULL')      &n= bsp;    =3D coalesce(rec1.date_entered,'NULL') and
     = ;       coalesce(sequence_number,'NULL')     =   =3D coalesce(rec1.sequence_number,'NULL'
);
         v_r= ows :=3D sql%rowcount;
         if v_rows !=3D 1 then
  &nb= sp;         v_Ret :=3D 1;
          &= nbsp; pr_write_error_log( sys_context('userenv','session_user'),
    &nb= sp;                     &= nbsp;     sys_context('userenv','host'), v_Module,
     = ;                     &nb= sp;     0, 'UPDATE processed ' || v_rows || ', expected 1' );
  &nb= sp;      end if;
      else
        &nb= sp;v_Ret :=3D 1;
         PERFORM pr_write_error_log( sys= _context('userenv','session_user'),
         sys_context(= 'userenv','host'), v_Module,
         0, 'FAILED to proce= ss: ' ||
         rec1.Receipt_Number || ', ' || rec1.Dat= e_Entered || ', ' || rec1.Sequence_Number );
      end if;
    &= nbsp; exit when v_Ret !=3D 0 or SQLCODE !=3D 0;
   end loop;

   if v_Ret =3D 0 then
  &nbs= p;   v_Ret :=3D SQLCODE;
   end if;

exception
   when others then
      v_ErrorCod= e :=3D SQLCODE;
      v_ErrorMsg  :=3D SQLERRM;

      if inserting then=
 =        v_ddl :=3D 'INSERT';
      elsif updati= ng then
         v_ddl :=3D 'UPDATE';
      else<= /div>
  =        v_ddl :=3D 'UNKNOWN';
      end if;

      PERFORM p= r_write_error_log( sys_context('userenv','session_user'),
      sys= _context('userenv','host'), v_Module,
      0, v_ddl || ' encounter= ed FALTAL ERROR: (' || v_ErrorCode || '): ' || v_ErrorMsg );

      --dbms_output.put= _line('FATAL ERROR while ' || v_ddl || ':  Encountered (' || v_ErrorCo= de || ') : ' || v_ErrorMsg );
      --dbms_output.put_line('FATAL E= RROR:  Record ' || nvl(v_Rec_Num,'null') || '~' || nvl(v_Dat_Ent,'null= ') || '~' || nvl(v_Seq_Num,'null') );
end;
$$ LANGUAGE plpgsql;


=
ERROR:  relation "c1" does not exist
CONTEXT:  = compilation of PL/pgSQL function "PR_MIG_STG_I864" near line 17
SQL state: 42P01
 
 I found the %rowtype could d= eclare of the table%rowtype,my= row tablename%ROWTYPE; but my oracle script had union tables, h= ow can I declare it?  I have tried declare rec1 stg%rowtype or prod%ro= wtype and/or the cidr_staging.stg_i864%rowtype... the error is still same, = relation does not exist.

Any helps and explanation would be = appreciated.  

v/r,

Bach-Nga

N= o one in this world is pure and perfect.  If you avoid people for thei= r mistakes you will be alone. So judge less, love and forgive more.<= /span>
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 out= ward appearances. But to those who knew him well, he was a perfect gentlema= n (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.<= /span>
*** Genuinely rich **= * Faithful talent *** Sharing success
=
------=_Part_1326360_1926280867.1541732091646--