TTP 43425 – Greensboro Cycle/Route Updates – Notes

Table layouts with relevant columns (for my reference)

TABLE toad.ttp43425_load
 Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 ACCTNUM                                            NUMBER(12)                  
 METERNUM                                           VARCHAR2(12)                
 ERTNUM                                             VARCHAR2(12)                
 PREMNUM                                            NUMBER(12)                  
 PREMADDR                                           VARCHAR2(35)                
 CURCYCLE                                           NUMBER(4)                   
 CURROUTE                                           NUMBER(4)                   
 NEWCYCLE                                           NUMBER(4)                   
 NEWROUTE                                           NUMBER(4)

TABLE grncis.fciaprem
 Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 ACC_RN                                    NOT NULL NUMBER(10)                  
 APREM_COMP                                NOT NULL NUMBER(2)                   
 APREM_NO                                  NOT NULL NUMBER(11)                  
 APREM_APPLICATION                         NOT NULL NUMBER(2)                   
 APREM_CYCLE                                        NUMBER(4)                   
 APREM_ROUTE                                        NUMBER(4)                   

Total loaded file 

select count(*) from toad.ttp43425_load;

  COUNT(*)
----------
     83681
1 row selected.




FWACMAS Update with FCIAPREM as driving table in conjunction with the “loaded” table (“Loaded” table means loaded from the supplied spreadsheet data)


select count(*) from grncis.fwacmas w 
join toad.ttp43425_load t on (t.acctnum = w.wacm_cust);



  COUNT(*)   (MATCHES THE NUMBER of RECORDS in INPUT FILE)
----------
     83681
1 row selected.



select count(distinct(wacm_cust)) from grncis.fwacmas w
join toad.ttp43425_load t on ((t.acctnum = w.wacm_cust)
and (T.PREMNUM =w.wacm_premise_no));
83632

select count(*) from toad.ttp43425_load t 
where exists
(select distinct w.wacm_cust from grncis.fwacmas w where t.acctnum=w.wacm_cust and t.premnum = w.wacm_premise_no);

83632

create table toad.ttp43425_upd_FWACMAS as 
select * from toad.ttp43425_load t
where exists
(select distinct w.wacm_cust from grncis.fwacmas w where t.acctnum=w.wacm_cust and t.premnum = w.wacm_premise_no);
83632

select count(m.aprem_application), m.aprem_application from grncis.fciaprem m
join toad.ttp43425_load t on (t.premnum = m.aprem_no)
and (T.CURROUTE = M.APREM_ROUTE)
and (t.curcycle = M.APREM_CYCLE)
join grncis.fwacmas w on (m.aprem_no=w.wacm_premise_no)
join toad.ttp43425_load t2 on (t2.acctnum = w.wacm_cust)
group by m.aprem_application;


COUNT(M.APREM_APPLICATION) APREM_APPLICATION
-------------------------- -----------------
                     89356                 6
                     88874                 8
                     90444                 3
                     90164                 4

4 rows selected.


COUNT(M.APREM_APPLICATION) APREM_APPLICATION
-------------------------- -----------------
                     89356                 6
                     88874                 8
                     90164                 4

3 rows selected.

select count(wa.wacm_premise_no) from grncis.fwacmas wa 
join grncis.fciaprem p on (p.aprem_no = wa.wacm_premise_no)
join toad.ttp43425_load t on (t.acctnum = wa.wacm_cust)
and p.aprem_application=3;
83681

select count(wa.wacm_premise_no) from grncis.fwacmas wa 
join grncis.fciaprem p on (p.aprem_no = wa.wacm_premise_no)
join toad.ttp43425_load t on (t.acctnum = wa.wacm_cust)
and (t.premnum = p.aprem_no)
and p.aprem_application=3;
83632

select count(*) from toad.ttp43425_fwacmas_accrn;
create table toad.ttp43425_fwacmas_accrn
as 
select wa.acc_rn, wa.wacm_cust, wa.wacm_premise_no
from grncis.fwacmas wa 
join grncis.fciaprem p on (p.aprem_no = wa.wacm_premise_no)
join toad.ttp43425_load t on (t.acctnum = wa.wacm_cust)
and (t.premnum = p.aprem_no)
and p.aprem_application=3;
83632

select count(wa.wacm_premise_no) from grncis.fwacmas wa 
join grncis.fciaprem p on (p.aprem_no = wa.wacm_premise_no)
join toad.ttp43425_load t on (t.acctnum = wa.wacm_cust)
and (t.curroute = p.aprem_route)
and (t.curcycle = p.aprem_cycle)
and p.aprem_application=3;
83681


select w.wacm_cust from grncis.fwacmas w 
join toad.ttp43425_load t on (t.acctnum = w.wacm_cust)
and (t.premnum = w.wacm_premise_no)
MINUS
select wa.wacm_cust  from grncis.fwacmas wa 
join grncis.fciaprem p on (p.aprem_no = wa.wacm_premise_no)
join toad.ttp43425_load t on (t.acctnum = wa.wacm_cust)
and (t.premnum = p.aprem_no)
and p.aprem_application=3;
83632 from each – the MINUS yields 0, so the simpler query can be used for the update.
Cr_FWACMAS_Upd_Cycle.sql
And FWACMAS_Upd_Cycle.sql in /home/oracle/carol
set pause off echo off term off verify off serveroutput off pages 66 feedback off lines 255 trimspool on


set heading off;
set linesize 300;

select 'update grncis.fwacmas w set w.wacm_cycle = ' || t.newcycle || ' where w.wacm_cust = ' || t.acctnum || ' and
w.wacm_premise_no = ' || t.premnum || ';'
from toad.ttp43425_upd_FWACMAS t;
83632 update statements

select 'update toad.ttp43425_load t set t.acc_rn = ' || w.acc_rn || ' where t.acct_num = '  || w.wacm_cust || 
' and t.premnum = ' || w.wacm_premise_no || ';' 
from grncis.fwacmas w;


set heading off;
set linesize 300;


 grncis.fwacmas w set w.wacm_cycle = ' || t.newcycle || ' where w.acc_rn = '|| t.acc_rn || ';'
from toad.ttp43425_load t;

select 'commit' from dual;



 FSWCMAS

select count(*) from grncis.fswcmas sw 
join toad.ttp43425_load t on (t.acctnum + 100 = sw.swcm_cust);
  COUNT(*)
----------
     78955
1 row selected.

select count(distinct(swcm_cust)) from grncis.fswcmas s
join toad.ttp43425_load t on ((t.acctnum +100 = s.swcm_cust)
and (T.PREMNUM =s.swcm_premise_no));

  COUNT(*)
----------
     78955
1 row selected.

select count(*) from toad.ttp43425_load t 
where exists
(select distinct s.swcm_cust from grncis.fswcmas s where t.acctnum +100 =s.swcm_cust and t.premnum = s.swcm_premise_no);


  COUNT(*)
----------
     78912
1 row selected.

select count(s.swcm_premise_no) from grncis.fswcmas s 
join grncis.fciaprem p on (p.aprem_no = s.swcm_premise_no)
join toad.ttp43425_load t on (t.acctnum +100 = s.swcm_cust)
and p.aprem_application=4;

COUNT(S.SWCM_PREMISE_NO)
------------------------
                   78955
1 row selected.

select count(sw.swcm_premise_no) from grncis.fswcmas sw 
join grncis.fciaprem p on (p.aprem_no = sw.swcm_premise_no)
join toad.ttp43425_load t on (t.acctnum +100 = sw.swcm_cust)
and (t.premnum = p.aprem_no)
and p.aprem_application=4;


COUNT(SW.SWCM_PREMISE_NO)
-------------------------
                    78912
1 row selected.

select count(sw.swcm_premise_no) from grncis.fswcmas sw 
join grncis.fciaprem p on (p.aprem_no = sw.swcm_premise_no)
join toad.ttp43425_load t on (t.acctnum +100 = sw.swcm_cust)
and (t.curroute = p.aprem_route)
and (t.curcycle = p.aprem_cycle)
and p.aprem_application=4;


COUNT(SW.SWCM_PREMISE_NO)
-------------------------
                    78953
1 row selected.

select sw.swcm_cust from grncis.fswcmas sw 
join toad.ttp43425_load t on (t.acctnum +100= sw.swcm_cust)
and (t.premnum = sw.swcm_premise_no)
MINUS
select sw.swcm_cust  from grncis.fswcmas sw 
join grncis.fciaprem p on (p.aprem_no = sw.swcm_premise_no)
join toad.ttp43425_load t on (t.acctnum +100 = sw.swcm_cust)
and (t.premnum = p.aprem_no)
and p.aprem_application=4;
No Rows Selected with the MINUS, 
78912 rows to be updated so the simpler account number/premise number query can be used to update the master records.

set pause off echo off term off verify off serveroutput off pages 66 feedback off lines 255 trimspool on

set heading off;
set linesize 300;


select 'update toad.ttp43425_load t set t.swacc_rn = ' || sw.acc_rn || ' where t.acctnum + 100 = '
|| sw.swcm_cust || ' and t.premnum = ' || sw.swcm_premise_no || ';'
from grncis.fswcmas sw;


select 'commit;' from dual;

FRFCMAS

select count(*) from grncis.frfcmas rf 
join toad.ttp43425_load t on (t.acctnum + 300 = rf.rfcm_cust);


  COUNT(*)
----------
     78455
1 row selected.

select count(distinct(rfcm_cust)) from grncis.frfcmas rf
join toad.ttp43425_load t on ((t.acctnum +300 = rf.rfcm_cust)
and (T.PREMNUM =rf.rfcm_premise_no));

COUNT(DISTINCT(RFCM_CUST))
--------------------------
                     78432
1 row selected.

select count(*) from toad.ttp43425_load t 
where exists
(select distinct rf.rfcm_cust from grncis.frfcmas rf where t.acctnum +300 =rf.rfcm_cust and t.premnum = rf.rfcm_premise_no);


COUNT(DISTINCT(RFCM_CUST))
--------------------------
                     78432
1 row selected.

select count(rf.rfcm_premise_no) from grncis.frfcmas rf 
join grncis.fciaprem p on (p.aprem_no = rf.rfcm_premise_no)
join toad.ttp43425_load t on (t.acctnum +300 = rf.rfcm_cust)
and p.aprem_application=6;

  COUNT(*)
----------
     78455
1 row selected.

select count(rf.rfcm_premise_no) from grncis.frfcmas rf 
join grncis.fciaprem p on (p.aprem_no = rf.rfcm_premise_no)
join toad.ttp43425_load t on (t.acctnum +300 = rf.rfcm_cust)
and (t.premnum = p.aprem_no)
and p.aprem_application=6;


COUNT(RF.RFCM_PREMISE_NO)
-------------------------
                    78432
1 row selected.

select rf.rfcm_cust from grncis.frfcmas rf 
join toad.ttp43425_load t on (t.acctnum +300= rf.rfcm_cust)
and (t.premnum = rf.rfcm_premise_no)
MINUS
select rf.rfcm_cust  from grncis.frfcmas rf 
join grncis.fciaprem p on (p.aprem_no = rf.rfcm_premise_no)
join toad.ttp43425_load t on (t.acctnum +300 = rf.rfcm_cust)
and (t.premnum = p.aprem_no)
and p.aprem_application=6;

Minus yields no rows – simpler updates




set pause off echo off term off verify off serveroutput off pages 66 feedback off lines 255 trimspool on

set heading off;
set linesize 300;


select 'update toad.ttp43425_load t set t.rfacc_rn = ' || rf.acc_rn || ' where t.acctnum + 100 = '
|| rf.rfcm_cust || ' and t.premnum = ' || rf.rfcm_premise_no || ';’ 
from grncis.frfcmas rf;



set heading off;
set linesize 300;


select 'update grncis.frfcmas rf set rf.rfcm_cycle = ' || t.newcycle || ' where rf.acc_rn = '|| t.rfacc_rn || ';'
from toad.ttp43425_load t;


FDGMAS

select count(*) from grncis.fdgcmas dg 
join toad.ttp43425_load t on (t.acctnum + 500 = dg.dgcm_cust);



  COUNT(*)
----------
     77963
1 row selected.




select count(distinct(dgcm_cust)) from grncis.fdgcmas s
join toad.ttp43425_load t on ((t.acctnum +500 = s.dgcm_cust)
and (T.PREMNUM =s.dgcm_premise_no));


COUNT(DISTINCT(DGCM_CUST))
--------------------------
                     77942
1 row selected.

select count(*) from toad.ttp43425_load t 
where exists
(select distinct dg.dgcm_cust from grncis.fdgcmas dg where t.acctnum +500 =dg.dgcm_cust and t.premnum = dg.dgcm_premise_no);


  COUNT(*)
----------
     77942
1 row selected.

select count(dg.dgcm_premise_no) from grncis.fdgcmas dg 
join grncis.fciaprem p on (p.aprem_no = dg.dgcm_premise_no)
join toad.ttp43425_load t on (t.acctnum +500 = dg.dgcm_cust)
and p.aprem_application=8;


COUNT(DG.DGCM_PREMISE_NO)
-------------------------
                    77963
1 row selected.

select count(dg.dgcm_premise_no) from grncis.fdgcmas dg 
join grncis.fciaprem p on (p.aprem_no = dg.dgcm_premise_no)
join toad.ttp43425_load t on (t.acctnum +500 = dg.dgcm_cust)
and (t.premnum = p.aprem_no)
and p.aprem_application=8;


COUNT(DG.DGCM_PREMISE_NO)
-------------------------
                    77942
1 row selected.

select dg.dgcm_cust from grncis.fdgcmas dg 
join toad.ttp43425_load t on (t.acctnum +500= dg.dgcm_cust)
and (t.premnum = dg.dgcm_premise_no)
MINUS
select dg.dgcm_cust  from grncis.fdgcmas dg 
join grncis.fciaprem p on (p.aprem_no = dg.dgcm_premise_no)
join toad.ttp43425_load t on (t.acctnum +500 = dg.dgcm_cust)
and (t.premnum = p.aprem_no)
and p.aprem_application=8;
no rows selected.

set pause off echo off term off verify off serveroutput off pages 66 feedback off lines 255 trimspool on

set heading off;
set linesize 500;


select 'update toad.ttp43425_load t set t.dgacc_rn = ' || dg.acc_rn || ' where t.acctnum + 500 = '
|| dg.dgcm_cust || ' and t.premnum = ' || dg.dgcm_premise_no || ';'
from grncis.fdgcmas dg;







FCICMTR –  83681 Update Statements for both Routes and Cycles



select count(*) from grncis.fcicmtr m
join toad.ttp43425_load t on (T.ACCTNUM = m.cmtr_cust)
join grncis.fciaprem p on (p.aprem_no = t.premnum)
and (t.curcycle = p.aprem_cycle)
and (t.curroute = p.aprem_route)
and p.aprem_application =3;
84686

select count(*) from grncis.fcicmtr m
join toad.ttp43425_load t on (T.ACCTNUM = m.cmtr_cust);
84739

	select count(m.cmtr_cust) from toad.ttp43425_load t, grncis.fcicmtr m, grncis.fciaprem p
where t.acctnum = m.cmtr_cust
and t.premnum = P.APREM_NO
and t.curcycle = p.aprem_cycle
and t.curroute = p.aprem_route
and p.aprem_application =3;

84686

select 'update grncis.fcicmtr m set m.cmtr_hh_route = ' || t.newroute || ' where m.cmtr_cust = ' || t.acctnum || ' ;' from
toad.ttp43425_load t, grncis.fcicmtr m, grncis.fciaprem p 
where t.acctnum = m.cmtr_cust
and t.premnum = P.APREM_NO
and t.curcycle = p.aprem_cycle
and t.curroute = p.aprem_route
and p.aprem_application =3;
84686

In /home/oracle/carol/FCICMTR_Upd_Cycle.sql and FCICMTR_Upd_Route.sql
set pause off echo off term off verify off serveroutput off pages 66 feedback off lines 255 trimspool on


set heading off;
set linesize 300;
select 'update grncis.fcicmtr m set m.cmtr_hh_route = ' || t.newroute || ' where m.cmtr_cust = ' || t.acctnum || ' ;' from
toad.ttp43425_load t;

-- set pause off echo off term off verify off serveroutput off pages 66 feedback off lines 255 trimspool on


set heading off;
set linesize 300;
select 'update grncis.fcicmtr m set m.cmtr_cycle = ' || t.newcycle || ' where m.cmtr_cust = ' || t.acctnum || ' ;' from
toad.ttp43425_load t;


set pause off echo off term off verify off serveroutput off pages 66 feedback off lines 255 trimspool on
set heading off;
set linesize 300;
select 'update grncis.fcicmtr m set m.cmtr_hh_route = ' || t.newroute || ' where m.cmtr_cust = ' || t.acctnum || 
' and m.cmtr_device = ' || t.meternum || ' and m.codid_1 = ' || t.ertnum || ' ;' from
toad.ttp43425_load t;
set pause off echo off term off verify off serveroutput off pages 66 feedback off lines 255 trimspool on
set heading off;
set linesize 300;
select 'update grncis.fswcmas m set m.swcm_cycle = ' || t.newcycle || ' where m.swcm_cust = ' || t.acctnum || 
' and m.cmtr_device = ' || t.meternum || ' and m.codid_1 = ' || t.ertnum || ' ;' from
toad.ttp43425_load t;

___________________________________________________________________________________________________________

FCIAPREM Table – for both Cycle and Route updates…


select count(*) from grncis.fciaprem m
join toad.ttp43425_load t on (T.premnum = M.aprem_no)
and (t.curcycle = m.aprem_cycle)
and (t.curroute= m.aprem_route)
and m.aprem_application=3
83632

select count(*) from toad.ttp43425_load t
join grncis.fciaprem m on (m.aprem_no = t.premnum)
and m.aprem_application=3;
83632
Double check the simple update stmnt can be used
select m.aprem_no from grncis.fciaprem m
join toad.ttp43425_load t on (T.premnum = M.aprem_no)
and m.aprem_application=3
MINUS
select t.premnum from toad.ttp43425_load t
join grncis.fciaprem m on (m.aprem_no = t.premnum)
and m.aprem_application=3;
No rows selected – so simple stmt can be userd.
cr_FCIAPREM_Upd_Route.sql
in /home/oracle/carol

FCIAPREM_Upd_Route.sql – script to run to do the updates.

For the Cycle updates:
cr_FCIAPREM_Upd_Cycle.sql

FCIAPREM_Upd_Cycle.sql
set pause off echo off term off verify off serveroutput off pages 66 feedback off lines 255 trimspool on


set heading off;
set linesize 300;

select 'update grncis.fciaprem p set p.aprem_route = ' || t.newroute || ' where  p.aprem_no = ' || t.premnum ||
' and p.aprem_application=3;' from toad.ttp43425_load t
  join grncis.fciaprem m on (m.aprem_no = t.premnum)
and m.aprem_application=3;
Similar statement for cycle updates

83632 update statements.



FCIMONTH Updates – Route only

TABLE grncis.fcimonth
 Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 ACC_RN                                    NOT NULL NUMBER(10)                  
 CIMTHTR_CODE                                       NUMBER(4)                   
 CIMTHTR_COMP                              NOT NULL NUMBER(2)                   
 CAPPLICATION_1                            NOT NULL NUMBER(2)                   
 CIMTHTR_CUST                              NOT NULL NUMBER(12)                  
 CIMTHTR_SEQ                               NOT NULL NUMBER(6)                   
 CACOMP_1                                           NUMBER(2)                   
 CIMTHTR_METER                                      VARCHAR2(12)                
 CIMTHTR_KVAR_METER                                 NUMBER(2)                   
 CIMTHTR_HH_ROUTE                                   NUMBER(7)          


select count(*) from grncis.fcimonth m
join toad.ttp43425_load t on (t.acctnum= m.cimthtr_cust);
19820

	select count(*) from grncis.fcimonth m
join toad.ttp43425_load t on (t.acctnum= m.cimthtr_cust)
and (t.meternum = m.cimthtr_meter);
0 – Meter number not relevant

select count(m.cimthtr_cust) from grncis.fcimonth m
join grncis.fwacmas w on (w.wacm_cust = cimthtr_cust) 
join grncis.fciaprem p on (p.aprem_no = w.wacm_premise_no)
join toad.ttp43425_load t on (t.acctnum = w.wacm_cust)
and (t.curroute = p.aprem_route)
and (t.curcycle = p.aprem_cycle)
and p.aprem_application=3;
19820
Test to see if simpler update stmt can be used
select m.cimthtr_cust from grncis.fcimonth m
join grncis.fwacmas w on (w.wacm_cust = cimthtr_cust) 
join grncis.fciaprem p on (p.aprem_no = w.wacm_premise_no)
join toad.ttp43425_load t on (t.acctnum = w.wacm_cust)
and (t.curroute = p.aprem_route)
and (t.curcycle = p.aprem_cycle)
and p.aprem_application=3
MINUS
select m.cimthtr_cust from grncis.fcimonth m
join toad.ttp43425_load t on (t.acctnum= m.cimthtr_cust);

MINUS yields 0 rows so simplest can be used.
cr_FCIMONTH_Upd_Route.sql
FCIMONTH_Upd_Route.sql
In /home/oracle/carol
set pause off echo off term off verify off serveroutput off pages 66 feedback off lines 255 trimspool on


set heading off;
set linesize 300;

select 'update grncis.fcimonth m set m.cimthtr_hh_route = ' || t.newroute || ' where m.cimthtr_cust = ' ||  t.acctnu
m || ';'
from toad.ttp43425_load t
join grncis.fcimonth m on (m.cimthtr_cust = t.acctnum);
19820


Checks
select count(*) from grncis.fcicmtr m
join toad.ttp43425_load t on (T.ACCTNUM = m.cmtr_cust)
and (t.curroute = m.cmtr_hh_route);
and m.cmtr_application =4;

select count(*) from grncis.fcicmtr m
join toad.ttp43425_load t on (T.ACCTNUM = m.cmtr_cust)
and (t.newroute = m.cmtr_hh_route)
--and (t.curroute = p.aprem_route)
and m.cmtr_application =4;


select count(*) from grncis.fciaprem m
join toad.ttp43425_load t on (T.premnum = M.aprem_no)
--and (t.curcycle = m.aprem_cycle)
and (t.curroute= m.aprem_route)
and m.aprem_application=3;

select count(*) from grncis.fciaprem m
join toad.ttp43425_load t on (T.premnum = M.aprem_no)
--and (t.newcycle = m.aprem_cycle)
and (t.newroute= m.aprem_route)
and m.aprem_application=3;