Build temporary tables to check data

/*
TRUNCATE TABLE Tmp_abc_fgsana;
DROP TABLE Tmp_abc_fgsana;
/
CREATE GLOBAL Temporary TABLE Tmp_abc_fgsana
(
Fgsid VARCHAR2 (64),
Fgsname VARCHAR2 (64),
Fldorder number,
BCel number,
Ccel number,
Dcel number,
Ecel number,
Fcel number,
Gcel number,
Hcel number,
ICel number,
Jcel number,
Kcel number,
Lcel number,
Mcel number,
Ncel number,
Ocel number,
Pcel number,
Qcel number,
Rcel number,
Scel number,
Tcel number,
Ucel number,
Vcel number,
Wcel number,
XCel number,
Ycel number,
Zcel number,
Aacel number,
Abcel number,
AcCel number
on COMMIT Preserve ROWS;
*/

TRUNCATE TABLE Tmp_abc_fgsana;
--sub-company name
Insert into Tmp_abc_fgsana (fgsid,fgsname,fldorder) Select Fldid,fldcareaname,fldorder from Tar_area where Fldparentid is null;
---
Total number of--1 buildings
Update Tmp_abc_fgsana t set bcel= (SELECT COUNT (*) from tar_station s where S.fldex9=t.fgsid and flditype are not null and F ldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 '));
--Total number of tenants
Update Tmp_abc_fgsana t set ccel= (select SUM (fldex38) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null a nd fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and fldex38!= ' unknown ');
Total completion number of--2G chamber
Update Tmp_abc_fgsana t set dcel= (SELECT COUNT (fldex17) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and fldex17= ' 1 ');
Total completion number of--3G chamber
Update Tmp_abc_fgsana t set ecel= (SELECT COUNT (fldex19) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and fldex19= ' 1 ');
Total completion Number of--wlan
Update Tmp_abc_fgsana t set fcel= (SELECT COUNT (fldex21) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and fldex21= ' 1 ');
--Total number of building lines completed
Update Tmp_abc_fgsana t set gcel= (SELECT COUNT (FLDEX29) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and fldex29= ' 6 ');
Total completion Number of--IPTV
Update Tmp_abc_fgsana t set hcel= (SELECT COUNT (FLDEX67) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and fldex67= ' 1 ');
Total number of--a buildings
Update Tmp_abc_fgsana t set icel= (SELECT COUNT (*) from tar_station s where S.fldex9=t.fgsid and flditype are not null and F ldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (7,12,8,9,3,11,5,2,13,1,10,20));
Total number of tenants in the--a class
Update Tmp_abc_fgsana t set jcel= (select SUM (fldex38) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null a nd fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (7,12,8,9,3,11,5,2,13,1,10,20) and fldex38!= ' unknown ');
--a class Two G-cell quantity
Update Tmp_abc_fgsana t set kcel= (SELECT COUNT (fldex17) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (7,12,8,9,3,11,5,2,13,1,10,20) and fldex17= ' 1 ');
--a class three G-cell quantity
Update Tmp_abc_fgsana t set lcel= (SELECT COUNT (fldex19) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (7,12,8,9,3,11,5,2,13,1,10,20) and fldex19= ' 1 ');
--A Type WLAN
Update Tmp_abc_fgsana t set mcel= (SELECT COUNT (fldex21) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (7,12,8,9,3,11,5,2,13,1,10,20) and fldex21= ' 1 ');
Total number of--a-type building line
Update Tmp_abc_fgsana t set ncel= (SELECT COUNT (FLDEX29) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (7,12,8,9,3,11,5,2,13,1,10,20) and fldex29= ' 6 ');
Total number of--a class IPTV
Update Tmp_abc_fgsana t set ocel= (SELECT COUNT (FLDEX67) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (7,12,8,9,3,11,5,2,13,1,10,20) and fldex67= ' 1 ');
---==b
Total number of--b buildings
Update Tmp_abc_fgsana t set pcel= (SELECT COUNT (*) from tar_station s where S.fldex9=t.fgsid and flditype are not null and F ldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (6,14,15,33,19,21,22,4));
Total number of tenants in the--b class
Update Tmp_abc_fgsana t set qcel= (select SUM (fldex38) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null a nd fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (6,14,15,33,19,21,22,4) and fldex38!= ' unknown ');
--b class Two G-cell quantity
Update Tmp_abc_fgsana t set rcel= (SELECT COUNT (fldex17) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (6,14,15,33,19,21,22,4) and fldex17= ' 1 ');
--b class three G-cell quantity
Update Tmp_abc_fgsana t set scel= (SELECT COUNT (fldex19) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (6,14,15,33,19,21,22,4) and fldex19= ' 1 ');
--b Type WLAN
Update Tmp_abc_fgsana t set tcel= (SELECT COUNT (fldex21) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (6,14,15,33,19,21,22,4) and fldex21= ' 1 ');
Total number of--b-type building line
Update Tmp_abc_fgsana t set ucel= (SELECT COUNT (FLDEX29) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (6,14,15,33,19,21,22,4) and fldex29= ' 6 ');
Total number of--b class IPTV
Update Tmp_abc_fgsana t set vcel= (SELECT COUNT (FLDEX67) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (6,14,15,33,19,21,22,4) and fldex67= ' 1 ');
------------C
Total number of tenants in the--c class
Update Tmp_abc_fgsana t set wcel= (select SUM (fldex38) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null a nd fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (18,29,24,26,17,25,27,28,32,30,31) and fldex38!= ' unknown ');
--c class Two G-cell quantity
Update Tmp_abc_fgsana t set xcel= (SELECT COUNT (fldex17) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (18,29,24,26,17,25,27,28,32,30,31) and fldex17= ' 1 ');
--c class three G-cell quantity
Update Tmp_abc_fgsana t set ycel= (SELECT COUNT (fldex19) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (18,29,24,26,17,25,27,28,32,30,31) and fldex19= ' 1 ');
--C Type WLAN
Update Tmp_abc_fgsana t set zcel= (SELECT COUNT (fldex21) from Tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (18,29,24,26,17,25,27,28,32,30,31) and fldex21= ' 1 ');
Total number of--c buildings
Update Tmp_abc_fgsana t set aacel= (SELECT COUNT (*) from tar_station s where S.fldex9=t.fgsid and flditype are NOT null and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (18,29,24,26,17,25,27,28,32,30,31));
Total number of--c-type building line
Update Tmp_abc_fgsana t set abcel= (SELECT COUNT (FLDEX29) from Tar_station s where S.fldex9=t.fgsid and Flditype are not nul L and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (18,29,24,26,17,25,27,28,32,30,31) and fldex29= ' 6 ');
Total number of--c class IPTV
Update Tmp_abc_fgsana t set accel= (SELECT COUNT (FLDEX67) from Tar_station s where S.fldex9=t.fgsid and Flditype are not nul L and fldisfilling= ' 1 ' and fldbuildstatus Not in (' 5 ', ' 3 ')
and Flditype in (18,29,24,26,17,25,27,28,32,30,31) and fldex67= ' 1 ');
--Query
SELECT * from Tmp_abc_fgsana order by Fldorder;