格式化----预处理----文件入库平衡稽核查询脚本

           忙了一天多的时间，终于搞定这个稍微复杂一点的查询脚本了。

 

              --描述 格式化，预处理，文件入库，分别用上一环节的正常输出文件数和本环节的正常输入文件数进行比对

--格式化

with format as
(select  2000 module_code,'格式化' module_name,a.source_id, (select t.busi_desc from d_src_type_info  t where a.source_id=t.source_id) busi_desc,
a.recieve_time,nvl(a.all_record_cnt,0) all_record_cnt, nvl(a.all_file_cnt,0) all_file_cnt,
               nvl(b.ok_record_cnt,0) ok_record_cnt,nvl(b.ok_file_cnt,0) ok_file_cnt,
               nvl(c.err_record_cnt,0) err_record_cnt,nvl(c.err_file_cnt,0) err_file_cnt,
decode(a.all_record_cnt,(nvl(b.ok_record_cnt,0)+nvl(c.err_record_cnt,0)),'平衡','不平衡') record_balance,
decode(a.all_file_cnt,(nvl(b.ok_file_cnt,0) + nvl(c.err_file_cnt,0)),'平衡','不平衡') file_balance,1 flag
 from
(select source_id, substr(recieve_time, 0, 8) recieve_time,
       sum(record_count) all_record_cnt,count(*) all_file_cnt from d_sch_format where  record_count<>0
        group by source_id, substr(recieve_time, 0, 8))  a,
 
(select source_id, substr(recieve_time, 0, 8) recieve_time,
       sum(record_count) ok_record_cnt,count(*) ok_file_cnt from d_sch_format where deal_flag='Y' and record_count<>0
        group by source_id, substr(recieve_time, 0, 8)) b,
 
 (select source_id, substr(recieve_time, 0, 8) recieve_time,
       sum(record_count) err_record_cnt,count(*) err_file_cnt from d_sch_format where deal_flag='E' and record_count<>0
       group by source_id, substr(recieve_time, 0, 8)) c
 where a.source_id=b.source_id(+) and a.source_id=c.source_id(+)
   and a.recieve_time=b.recieve_time(+) and a.recieve_time=c.recieve_time(+) ) ,
  
 ---查询 format表中的file_id在 d_sch_wrtf表中的记录条数
 formatWrtf as
   (select f2.source_id,f2.recieve_time,f.deal_flag,f.input_count,f.file_id from d_sch_wrtf f,format f2 where
     f.file_id in(select file_id from d_sch_format m where m.source_id=f2.source_id and substr(m.recieve_time, 0, 8) =f2.recieve_time and m.record_count<>0) 
 
   ),
  
   ---预处理

   wrtf as
(select  2002 module_code,'预处理' module_name,a.source_id, (select t.busi_desc from d_src_type_info  t where a.source_id=t.source_id) busi_desc,
a.recieve_time,nvl(a.all_record_cnt,0) all_record_cnt, nvl(a.all_file_cnt,0) all_file_cnt,
               nvl(b.ok_record_cnt,0) ok_record_cnt,nvl(b.ok_file_cnt,0) ok_file_cnt,
               nvl(c.err_record_cnt,0) err_record_cnt,nvl(c.err_file_cnt,0) err_file_cnt,
decode(a.all_record_cnt,(nvl(b.ok_record_cnt,0)+nvl(c.err_record_cnt,0)),'平衡','不平衡') record_balance,
decode(a.all_file_cnt,(nvl(b.ok_file_cnt,0) + nvl(c.err_file_cnt,0)),'平衡','不平衡') file_balance,2 flag
 from
 (select f2.source_id,f2.recieve_time,
         sum(f2.input_count) all_record_cnt,count(*) all_file_cnt
         from formatWrtf f2 group by f2.source_id,f2.recieve_time)  a,
        
  (select f2.source_id,f2.recieve_time,
         sum(f2.input_count) ok_record_cnt,count(*) ok_file_cnt
          from formatWrtf f2 where f2.deal_flag='Y' group by f2.source_id,f2.recieve_time)  b,
 
  (select f2.source_id,f2.recieve_time,
         sum(f2.input_count) err_record_cnt,count(*) err_file_cnt
          from formatWrtf f2  where f2.deal_flag='E' group by f2.source_id,f2.recieve_time)  c
 
 
 where a.source_id=b.source_id(+) and a.source_id=c.source_id(+)
   and a.recieve_time=b.recieve_time(+) and a.recieve_time=c.recieve_time(+)  ) ,
  
  ----文件入库
  ----即查询d_sch_indb表的file_id在临时表formatWrtf中的记录数
  formatIndb as
  (
     select f2.source_id,f2.recieve_time,f.deal_flag,f.mainflow_count,f.file_id from d_sch_indb f,format f2 where
     f.file_id in(select file_id from d_sch_format m where m.source_id=f2.source_id and substr(m.recieve_time, 0, 8) =f2.recieve_time)
  ),
 
  indb as
(select  2004 module_code,'文件入库' module_name,a.source_id, (select t.busi_desc from d_src_type_info  t where a.source_id=t.source_id) busi_desc,
a.recieve_time,nvl(a.all_record_cnt,0) all_record_cnt, nvl(a.all_file_cnt,0) all_file_cnt,
               nvl(b.ok_record_cnt,0) ok_record_cnt,nvl(b.ok_file_cnt,0) ok_file_cnt,
               nvl(c.err_record_cnt,0) err_record_cnt,nvl(c.err_file_cnt,0) err_file_cnt,
decode(a.all_record_cnt,(nvl(b.ok_record_cnt,0)+nvl(c.err_record_cnt,0)),'平衡','不平衡') record_balance,
decode(a.all_file_cnt,(nvl(b.ok_file_cnt,0) + nvl(c.err_file_cnt,0)),'平衡','不平衡') file_balance,3 flag
 from
 (select f2.source_id,f2.recieve_time,
         sum(f2.mainflow_count) all_record_cnt,count(*) all_file_cnt
         from formatIndb f2 group by f2.source_id,f2.recieve_time)  a,
        
  (select f2.source_id,f2.recieve_time,
         sum(f2.mainflow_count) ok_record_cnt,count(*) ok_file_cnt
          from formatIndb f2 where f2.deal_flag='Y' group by f2.source_id,f2.recieve_time)  b,
 
  (select f2.source_id,f2.recieve_time,
         sum(f2.mainflow_count) err_record_cnt,count(*) err_file_cnt
          from formatIndb f2  where f2.deal_flag='E' group by f2.source_id,f2.recieve_time)  c
 
 where a.source_id=b.source_id(+) and a.source_id=c.source_id(+)
   and a.recieve_time=b.recieve_time(+) and a.recieve_time=c.recieve_time(+) ),
 
 /*
 temprow as
 (
     select  2006 module_code,' ' module_name,a.source_id, ' ' busi_desc,
              a.recieve_time,
              null all_record_cnt,
              null all_file_cnt,
              null ok_file_cnt,
              null ok_record_cnt,
              null err_file_cnt,
              null err_record_cnt,
              null record_balance,
              null file_balance,
              4 flag
     from indb a
 
 ), */
 
 
 
 
 tempView as
 (
     select * from (
             select * from format
             union all
             select
                   w.module_code,
                   w.module_name,
                   w.source_id,
                   w.busi_desc,
                   w.recieve_time,
                   w.all_record_cnt,
                   w.all_file_cnt,
                   w.ok_record_cnt,
                   w.ok_file_cnt,
                   w.err_record_cnt,
                   w.err_file_cnt,
                   decode(w.all_record_cnt,f.ok_record_cnt,'平衡','不平衡')   record_balance,
                   decode(w.all_file_cnt,f.ok_file_cnt,'平衡','不平衡') file_balance,
                   w.flag
              from  wrtf w,format f where w.source_id=f.source_id and w.recieve_time=f.recieve_time
              union all
              select
                   d.module_code,
                   d.module_name,
                   d.source_id,
                   d.busi_desc,
                   d.recieve_time,
                   d.all_record_cnt,
                   d.all_file_cnt,
                   d.ok_record_cnt,
                   d.ok_file_cnt,
                   d.err_record_cnt,
                   d.err_file_cnt, 
                   decode(d.all_record_cnt,f.ok_record_cnt,'平衡','不平衡')   record_balance,
                   decode(d.all_file_cnt,f.ok_file_cnt,'平衡','不平衡') file_balance,
                   d.flag
               from indb d, wrtf f where d.source_id=f.source_id and d.recieve_time=f.recieve_time ) c
 )

select  source_id,
        busi_desc,
        module_name,
        recieve_time file_name,
        all_record_cnt in_record_cnt,
        ok_record_cnt,
        err_record_cnt,
        all_file_cnt in_file_cnt,
        ok_file_cnt,
        err_file_cnt,
        record_balance,
        file_balance,
        flag
  from tempView  order by recieve_time, source_id, flag;
 
 
 
 

 

 

 

                                                                                                                                  . eshore.