Camp data quality verification, about marketing MIS system and Distribution GIS system SQL statement query, as the accumulation of use, the next time you do not have to repeat the same statement.

1. Distribution GIS Line Export data:

Select R.name Line Name, r.run_status operating status, R.voltage_level voltage level,
R.manager_depart Management Department, R.belong_substation-owned factory station
From ODS_SC. T_d2_pd_feeder R where r.gsdm= ' 040100 ' and R.run_status <> ' return ' and r.voltage_level= ' 10kV '
and R.manager_depart not in
(' Jiangnan power supply Bureau ', ' Green Show Power Division ', ' Xingning power Supply Division ', ' West power supply Bureau ', ' five Elephant Power Division ')

2. Distribution GIS Transformer Export data

Select T2.name transformer name, T2. Belong_feeder belongs to the line, T2. Run_status running state,
T2. Manager_depart Management department, T2. Transformer_type transformer type, t2. BYQH transformer number, T2. Voltage_level Voltage Rating
From Ods_yx.v_gis_byq_count T2 where T2. Gsdm= ' 040100 ' and
T2.voltage_level= ' 10kV '
and (T2.manager_depart not in
(' Jiangnan power supply Bureau ', ' Green Show Power Branch ', ' Xingning power supply Bureau ', ' West power supply Bureau ', ' five Elephant Power Station ') or T2. Manager_depart is null) and
T2.transformer_type in (' Transformation ', ' male change ', ' special Change ')

3. Distribution GIS System user export data

Select User_no user number, user_name user name, X.manager_depart administration from
Ods_yx. V_gis_user_yhyzx x where x.gsdm= ' 040100 ' and X.manager_depart is null

4. Marketing MIS Line export data

Select L1.line_name Line Name,
(select Max (param_value) from Ods_yx.sys_param_value v where v.gdjdm= ' 040100 ' and V.param_code=l1.volt_level_code
and v.param_type_code= ' volt_level ') voltage rating,
(select Max (param_value) from Ods_yx.sys_param_value v where v.gdjdm= ' 040100 ' and V.param_code=l1.line_type
and v.param_type_code= ' Line_type ') line nature,
(select Max (next_dept_name) from Ods_yx. View_next_busi v where v.gdjdm= ' 040100 ' and
V.next_dept_code=l1.business_place_code) Business Area
From Ods_yx.line L1, where L1.business_place_code in (#)
and L1.volt_level_code= ' and L1.line_type in (1,5,10)

5. Marketing MIS Transformer export data

 select Trans_no, user_no number, Desk_name transformer name,
  (select Max (next_dept_name) from Ods_yx. View_next_busi v where v.gdjdm= ' 040100 ' and
  V.next_dept_code=n.business_place_code) belong to the business area,
  ( Select Max (param_value) from Ods_yx.sys_param_value v where v.gdjdm= ' 040100 ' and v.param_code=n.trans_state
  and v.param_type_code= ' Trans_status_flag ') transformer operating state,
  (select Max (param_value) from Ods_yx.sys_param_value V where v.gdjdm= ' 040100 ' and V.param_code=n.is_pub_trans
 and v.param_type_code= ' Is_pub_trans ') public variable type,
  (select Max (param_value) from Ods_yx.sys_param_value v where v.gdjdm= ' 040100 ' and V.param_code=n.volt_level_code
 and v.param_type_code= ' volt_level ') voltage level
 from ods_yx.trans_run n where N.business_place_code in (#) and N.volt_level_code= '
 6. Marketing MIS user export data

Select T1.user_no user number, T1.user_name user name,
(select Max (param_value) from Ods_yx.sys_param_value v where v.gdjdm= ' 040100 ' and v.param_code=t1.user_state
and v.param_type_code= ' user_status ') User state,
(select Max (next_dept_name) from Ods_yx. View_next_busi v where v.gdjdm= ' 040100 ' and
V.next_dept_code=t1.business_place_code) Business area,
(select Max (param_value) from Ods_yx.sys_param_value v where v.gdjdm= ' 040100 ' and V.param_code=t2.self_supply_mode
and v.param_type_code= ' Self_supply_mode ') power supply mode
From Ods_yx.user_files T1,ods_yx.power_file T2 where T1.user_no=t2.user_no and
T1.business_place_code in (#) and t1.user_state<> ' 2 '
and T2.self_supply_mode in (' 5 ', ' 6 ')

7. Production MIS system parameter information

Select Ywbm,nr from pd_gy_sbzdb where sjbm= ' 5012 ' device status

--Distribution Station information
SELECT * from PD_GY_SB_PDZLXXB where ID in (the Select ID from PD_GY_SB_WJJGB where bs= ' BDZ ');
--Voltage Rating
SELECT YWBM, nrfrom pd_gy_sbzdb WHERE sjbm = ' 5004 ';
--Property rights nature
SELECT GY_DM_XTDMB. YWBM, GY_DM_XTDMB. NR from Gy_dm_xtdmb WHERE GY_DM_XTDMB. SJBM = ' 371 '

8. County-level production MIS repeat transformer number

Select ID,BYQH as transformer number, WLSBMC as physical device name, SBXSLJ as device display path,
(select Max (XLMC) from DW_SCXJ.PD_GY_SB_XLXXB b where B.id=ssxl and gsdm= ' 040102 ') as-owned line
From ODS_YX.XJ_SC_GB where Byqh in (select Byqh
From ODS_YX.XJ_SC_GB Tzb
where tzb.gsdm = ' 040102 ' GROUP by BYQH have count (1) >1
)

Camp data quality verification, about marketing MIS system and Distribution GIS system SQL statement query, as the accumulation of use, the next time you do not have to repeat the same statement.