[
https://issues.apache.org/jira/browse/TRAFODION-2444?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15899841#comment-15899841
]
Suresh Subbiah commented on TRAFODION-2444:
-------------------------------------------
DDL to reproduce
cqd allow_nullable_unique_key_constraint 'on' ;
create schema nanrui_1;
set schema nanrui_1;
create table C_IT_RUN
(
cons_it_id NUMERIC(16) not null,
it_id NUMERIC(16),
inst_loc CHAR(256) character set utf8,
inst_date char(24),
sort_code CHAR(8) character set utf8,
phase_code CHAR(8) character set utf8,
current_ratio_code CHAR(8) character set utf8,
volt_ratio_code CHAR(8) character set utf8,
priv_flag CHAR(8) character set utf8,
org_no CHAR(16) character set utf8,
winding_no CHAR(32) character set utf8,
inst_mode CHAR(8) character set utf8,
last_chk_date char(24),
rotate_cycle NUMERIC(5),
rotate_valid_date char(24),
chk_cycle NUMERIC(5),
made_no CHAR(32) character set utf8,
asset_no CHAR(32) character set utf8,
bar_code CHAR(32) character set utf8
)
STORE BY (SORT_CODE)
SALT USING 8 PARTITIONS
ATTRIBUTES ALIGNED FORMAT
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'SNAPPY',
MEMSTORE_FLUSH_SIZE = '1073741824'
);
-----------------
create table C_MP_IT_RELA
(
it_mp_id NUMERIC(16) not null,
cons_it_id NUMERIC(16),
mp_id NUMERIC(16),
cons_no CHAR(16) character set utf8,
cons_id NUMERIC(16),
it_id NUMERIC(16)
)
STORE BY (MP_ID,IT_ID)
SALT USING 8 PARTITIONS
ATTRIBUTES ALIGNED FORMAT
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'SNAPPY',
MEMSTORE_FLUSH_SIZE = '1073741824'
);
--------------------
create table C_BILL_RELA
(
rela_id NUMERIC(16) not null,
mp_id NUMERIC(16),
cons_id NUMERIC(16),
rela_sort_code CHAR(8) character set utf8,
rela_mp_id NUMERIC(16),
rela_ratio NUMERIC(12,4),
mp_direction_code CHAR(8) character set utf8,
rela_cons_id NUMERIC(16),
sub_rela CHAR(32) character set utf8
)
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'SNAPPY',
MEMSTORE_FLUSH_SIZE = '1073741824'
);
--------------
create table C_METER_MP_RELA
(
meter_mp_id NUMERIC(16) not null,
cons_mt_id NUMERIC(16),
mp_id NUMERIC(16),
cons_no CHAR(16) character set utf8,
cons_id NUMERIC(16),
meter_id NUMERIC(16),
org_no CHAR(16) character set utf8
)
STORE BY (METER_ID)
SALT USING 24 PARTITIONS
ATTRIBUTES ALIGNED FORMAT
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'SNAPPY',
MEMSTORE_FLUSH_SIZE = '1073741824'
);
-------------
create table C_PS
(
ps_id NUMERIC(16) not null,
sp_id NUMERIC(16),
ps_no CHAR(256) character set utf8,
cons_id NUMERIC(16),
type_code CHAR(256) character set utf8,
phase_code CHAR(8) character set utf8,
subs_id NUMERIC(16),
line_id NUMERIC(16),
tg_id NUMERIC(16),
ps_volt CHAR(8) character set utf8,
ps_cap NUMERIC(16,6),
ps_attr CHAR(8) character set utf8,
linein_mode CHAR(8) character set utf8,
linein_pole_no CHAR(32) character set utf8,
lv_box_no CHAR(32) character set utf8,
pr_point CHAR(256) character set utf8,
protect_mode CHAR(8) character set utf8,
run_mode CHAR(8) character set utf8,
relay_protect_mode CHAR(8) character set utf8,
remark CHAR(256) character set utf8
)
STORE BY (TYPE_CODE)
SALT USING 24 PARTITIONS
ATTRIBUTES ALIGNED FORMAT
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'SNAPPY',
MEMSTORE_FLUSH_SIZE = '1073741824'
);
------------------
create table G_LINE_TG_RELA
(
line_tq_id NUMERIC(16) not null,
tg_id NUMERIC(16),
line_id NUMERIC(16),
rela_flag CHAR(8) character set utf8 default '1'
)
store by (TG_ID)
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'SNAPPY',
MEMSTORE_FLUSH_SIZE = '1073741824'
);
------------------
create table C_CONS_PRC
(
tariff_id NUMERIC(16) not null,
sp_id NUMERIC(16),
cons_id NUMERIC(16),
pf_std_code CHAR(8) character set utf8,
trade_code CHAR(8) character set utf8,
prc_code CHAR(8) character set utf8,
ts_flag CHAR(8) character set utf8,
fix_ratio NUMERIC(10,6),
org_no CHAR(16) character set utf8
)
STORE BY (TARIFF_ID)
SALT USING 24 PARTITIONS
ATTRIBUTES ALIGNED FORMAT
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'SNAPPY',
MEMSTORE_FLUSH_SIZE = '1073741824'
);
--------------------
create table C_METER_READ
(
record_id NUMERIC(16) not null,
cons_mt_id NUMERIC(16),
use_id NUMERIC(16),
read_type_code CHAR(8) character set utf8,
"read" NUMERIC(12,4) default 0,
mr_digit NUMERIC(10,6),
mr_date char(24),
mr_pq NUMERIC(16),
mp_id NUMERIC(16),
cons_id NUMERIC(16),
cons_no CHAR(16) character set utf8,
check_type CHAR(8) character set utf8,
usage_code CHAR(8) character set utf8,
avg_pq NUMERIC(16),
meter_id NUMERIC(16),
mr_read NUMERIC(12,4),
org_no CHAR(16) character set utf8
)
STORE BY (METER_ID)
SALT USING 32 PARTITIONS
ATTRIBUTES ALIGNED FORMAT
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'SNAPPY',
MEMSTORE_FLUSH_SIZE = '1073741824'
);
---------------
--below is orc tables
create database porc;
use porc;
CREATE TABLE porc.C_MP(
mp_id double,
tariff_id double,
sp_id double,
mp_sect_id double,
mp_no string,
mp_name string,
mp_addr string,
type_code string,
mp_attr_code string,
usage_type_code string,
side_code string,
volt_code string,
app_date string,
run_date timestamp,
wiring_mode string,
meas_mode string,
org_no string,
switch_no string,
mr_sect_no string,
line_id double,
tg_id double,
exchg_type_code string,
md_type_code string,
mr_sn double,
mp_sn double,
meter_flag string,
status_code string,
lc_flag string,
cons_id double,
calc_mode string,
mp_level double,
fqr_value double,
fr_deduct_flag string,
deduct_order double,
tl_share_flag string,
ll_share_flag string,
tl_bill_flag string,
ll_bill_flag string,
ll_calc_mode string,
ap_ll_value double,
rp_ll_value double,
mp_cap double,
ll_share_value double,
tl_share_value double,
pt_ap_loss double,
pt_rp_loss double,
mr_share string,
mr_mode_code string,
cons_no string,
earth_mode string,
elec_kind_code string)
stored as ORC tblproperties ("orc.stripe.size"="67108864");
-------------------
CREATE TABLE D_METER(
meter_id double,
area_code string,
pr_org string,
belong_dept string,
contract_id double,
rcv_id double,
bar_code string,
lot_no string,
asset_no string,
made_no string,
sort_code string,
type_code string,
model_code string,
wiring_mode string,
volt_code string,
rated_current string,
overload_factor string,
ap_pre_level_code string,
rp_pre_level_code string,
meter_digits double,
ts_digits double,
const_code string,
rp_constant string,
manufacturer string,
made_date string,
eqip_prc double,
self_factor double,
both_way_calc string,
prepay_flag string,
multirate_falg string,
demand_meter_flag string,
harmonic_meas_falg string,
cc_prevent_flag string,
pulse_constant_code string,
pr_pulse_constant_code string,
pulse_amplitude_code string,
pulse_sort_code string,
freq_code string,
con_mode string,
reading_type_code string,
meter_usage string,
meas_theory string,
bearing_struc string,
ci string,
carrier_wave string,
congeal_flag string,
relay_joint string,
elec_meas_disp_flag string,
vl_flag string,
cl_flag string,
anti_phase_flag string,
super_power_flag string,
load_curve_flag string,
poweroff_mr_flag string,
infrared_flag string,
doc_type_code string,
latest_chk_date timestamp,
inst_date timestamp,
rmv_date string,
rotate_cycle double,
discard_reason string,
descard_date string,
pr_code string,
handover_dept string,
handover_date string,
cur_status_code string,
borrow_flag string,
new_flag string,
erp_batch_no string,
remark string,
wh_id double,
wh_area_id double,
store_area_id double,
store_loc_id double,
doc_create_date string,
box_bar_code string,
doc_creator_no string,
store_no string,
baudrate_code string,
meter_close_mode string,
register_mode string,
disp_mode string,
hard_ver string,
soft_ver string,
comm_prot_code string,
rs485_route_qty double,
comm_mode string,
attachequip_type_code string,
back_reason string,
made_standard string,
use_group string,
work_type string,
product_id string,
sample_type string,
meter_kind string,
project_no string,
price_type string,
carrier_wave_id double,
prc_code string,
cat_prc_name string,
kwh_prc double,
para_vn double)
stored as ORC tblproperties ("orc.stripe.size"="67108864");
----------------------
CREATE TABLE C_CONS(
cons_id double,
vat_id double,
cust_id double,
cons_no string,
cons_name string,
cust_query_no string,
tmp_pay_rela_no string,
orgn_cons_no string,
cons_sort_code string,
elec_addr string,
trade_code string,
elec_type_code string,
contract_cap double,
run_cap double,
shift_no string,
lode_attr_code string,
volt_code string,
hec_industry_code string,
holiday string,
build_date timestamp,
ps_date string,
cancel_date string,
due_date string,
notify_mode string,
settle_mode string,
org_no string,
rrio_code string,
chk_cycle double,
last_chk_date string,
checker_no string,
poweroff_code string,
transfer_code string,
mr_sect_no string,
note_type_code string,
tmp_flag string,
tmp_date string,
meas_mode string,
db_timestamp string,
card_no string,
charge_num double,
prio_code string,
remind_sect_no string,
amt_calc_id double)
partitioned by (status_code string)
stored as ORC tblproperties ("orc.stripe.size"="67108864");
---------------------
CREATE TABLE porc.C_METER(
cons_mt_id double,
meter_id double,
inst_loc string,
inst_date timestamp,
t_factor double,
ref_meter_flag string,
ref_meter_id double,
validate_code string,
module_no string,
org_no string,
mr_factor string,
last_chk_date string,
rotate_cycle double,
rotate_valid_date string,
sim_card_no string,
chk_cycle double,
bin_mt_pos string,
made_no string,
asset_no string,
bar_code string,
comm_addr1 string,
comm_addr2 string,
comm_no string,
baudrate string,
comm_mode string,
attachequip_type_code string,
meter_kind string)
stored as ORC tblproperties ("orc.stripe.size"="67108864");
------------------
upsert using load into trafodion.nanrui_1.t21_result
SELECT '$test_seq','$user_seq',count(*)
FROM(
SELECT '15.计量点相关信息数据准确性' item_detail,
case
when 10.0 - ((nvl(sum(RULL_01), 0) + nvl(sum(RULL_02), 0) +
nvl(sum(RULL_03), 0) + nvl(sum(RULL_04), 0) +
nvl(sum(RULL_05), 0) + nvl(sum(RULL_06), 0) +
nvl(sum(RULL_07), 0) + nvl(sum(RULL_08), 0) +
nvl(sum(RULL_09), 0) + nvl(sum(RULL_10), 0) +
nvl(sum(RULL_11), 0) + nvl(sum(RULL_13), 0) +
nvl(sum(RULL_14), 0) + nvl(sum(RULL_15), 0) +
nvl(sum(RULL_16), 0) + nvl(sum(RULL_17), 0) +
nvl(sum(RULL_19), 0) + nvl(sum(RULL_20), 0) +
nvl(sum(RULL_21), 0) + nvl(sum(RULL_22), 0) +
nvl(sum(RULL_23), 0) + nvl(sum(RULL_24), 0) +
nvl(sum(RULL_25), 0) + nvl(sum(RULL_26), 0) +
nvl(sum(RULL_27), 0) + nvl(sum(RULL_28), 0) +
nvl(sum(RULL_30), 0)) * 0.2 / (1000.0 * 27)) < 0 then
0
else
10.0 - ((nvl(sum(RULL_01), 0) + nvl(sum(RULL_02), 0) +
nvl(sum(RULL_03), 0) + nvl(sum(RULL_04), 0) +
nvl(sum(RULL_05), 0) + nvl(sum(RULL_06), 0) +
nvl(sum(RULL_07), 0) + nvl(sum(RULL_08), 0) +
nvl(sum(RULL_09), 0) + nvl(sum(RULL_10), 0) +
nvl(sum(RULL_11), 0) + nvl(sum(RULL_13), 0) +
nvl(sum(RULL_14), 0) + nvl(sum(RULL_15), 0) +
nvl(sum(RULL_16), 0) + nvl(sum(RULL_17), 0) +
nvl(sum(RULL_19), 0) + nvl(sum(RULL_20), 0) +
nvl(sum(RULL_21), 0) + nvl(sum(RULL_22), 0) +
nvl(sum(RULL_23), 0) + nvl(sum(RULL_24), 0) +
nvl(sum(RULL_25), 0) + nvl(sum(RULL_26), 0) +
nvl(sum(RULL_27), 0) + nvl(sum(RULL_28), 0) +
nvl(sum(RULL_30), 0)) * 0.2 / (1000.0 * 27))
end score_detail,
'RULL_01:' || nvl(sum(RULL_01), 0) || chr(10) || ',RULL_02:' ||
nvl(sum(RULL_02), 0) || chr(10) || ',RULL_03:' ||
nvl(sum(RULL_03), 0) || chr(10) || ',RULL_04:' ||
nvl(sum(RULL_04), 0) || chr(10) || ',RULL_05:' ||
nvl(sum(RULL_05), 0) || chr(10) || ',RULL_06:' ||
nvl(sum(RULL_06), 0) || chr(10) || ',RULL_07:' ||
nvl(sum(RULL_07), 0) || chr(10) || ',RULL_08:' ||
nvl(sum(RULL_08), 0) || chr(10) || ',RULL_09:' ||
nvl(sum(RULL_09), 0) || chr(10) || ',RULL_10:' ||
nvl(sum(RULL_10), 0) || chr(10) || ',RULL_11:' ||
nvl(sum(RULL_11), 0) || chr(10) || ',RULL_13:' ||
nvl(sum(RULL_13), 0) || chr(10) || ',RULL_14:' ||
nvl(sum(RULL_14), 0) || chr(10) || ',RULL_15:' ||
nvl(sum(RULL_15), 0) || chr(10) || ',RULL_16:' ||
nvl(sum(RULL_16), 0) || chr(10) || ',RULL_17:' ||
nvl(sum(RULL_17), 0) || chr(10) || ',RULL_19:' ||
nvl(sum(RULL_19), 0) || chr(10) || ',RULL_20:' ||
nvl(sum(RULL_20), 0) || chr(10) || ',RULL_21:' ||
nvl(sum(RULL_21), 0) || chr(10) || ',RULL_22:' ||
nvl(sum(RULL_22), 0) || chr(10) || ',RULL_23:' ||
nvl(sum(RULL_23), 0) || chr(10) || ',RULL_24:' ||
nvl(sum(RULL_24), 0) || chr(10) || ',RULL_25:' ||
nvl(sum(RULL_25), 0) || chr(10) || ',RULL_26:' ||
nvl(sum(RULL_26), 0) || chr(10) || ',RULL_27:' ||
nvl(sum(RULL_27), 0) || chr(10) || ',RULL_28:' ||
nvl(sum(RULL_28), 0) || chr(10) || ',RULL_30:' ||
nvl(sum(RULL_30), 0) data_detai
FROM (SELECT case when EXISTS (select 1
FROM trafodion.nanrui_1.C_BILL_RELA r
WHERE r.Mp_Id = m.MP_ID
AND NOT EXISTS
(SELECT 1
FROM hive.porc.c_mp mm
WHERE mm.MP_ID = r.RELA_MP_ID)) then 1 end
RULL_01,
case when EXISTS (select 1
from hive.porc.D_METER d,
trafodion.nanrui_1.C_Meter_MP_RELA r
WHERE m.MP_ID = r.MP_ID
AND d.METER_ID = r.METER_ID
AND d.AP_PRE_LEVEL_CODE = '03')
AND m.MD_TYPE_CODE = '1' then 1 end RULL_02,
case when EXISTS (SELECT 1
FROM trafodion.nanrui_1.C_PS p
WHERE p.TG_ID <> m.TG_ID
AND P.TYPE_CODE IN ('1', '01')
AND p.SP_ID = m.SP_ID) then 1 end RULL_03,
case when c.CONS_SORT_CODE <> '00'
and not exists (SELECT 1
FROM trafodion.nanrui_1.G_LINE_TG_RELA A
WHERE A.TG_ID = m.TG_ID
AND A.LINE_ID = m.LINE_ID) then 1 end RULL_04,
case when c.VOLT_CODE = 'AC00101'
AND m.MEAS_MODE = '2'
AND m.WIRING_MODE = '2' then 1 end RULL_05,
case when DECODE(m.VOLT_CODE,
'AC00031',
3,
'AC00061',
6,
'AC00101',
10,
'AC00201',
20,
'AC00351',
35,
'AC01101',
110,
'AC02201',
220,
'AC02202',
0.22,
'AC03301',
330,
'AC03802',
0.38,
'AC05001',
500,
'AC10002',
1,
'AC07501',
750,
0) > DECODE(c.VOLT_CODE,
'AC00031',
3,
'AC00061',
6,
'AC00101',
10,
'AC00201',
20,
'AC00351',
35,
'AC01101',
110,
'AC02201',
220,
'AC02202',
0.22,
'AC03301',
330,
'AC03802',
0.38,
'AC05001',
500,
'AC10002',
1,
'AC07501',
750,
0)then 1 end RULL_06,
case when c.TRANSFER_CODE = '2'
AND m.MP_LEVEL = 1 then 1 end RULL_07,
case when c.CONS_SORT_CODE = '03'
AND m.MEAS_MODE != '3' then 1 end RULL_08,
case when m.VOLT_CODE NOT IN ('AC02202', 'AC03802')
AND m.MEAS_MODE = '3' then 1 end RULL_09,
case when c.VOLT_CODE NOT IN ('AC02202', 'AC03802')
AND m.MEAS_MODE = '3' then 1 end RULL_10,
case when c.CONS_SORT_CODE <> '00'
and not exists
(SELECT 1
FROM trafodion.nanrui_1.C_CONS_PRC r
WHERE r.TARIFF_ID = m.TARIFF_ID) then 1 end RULL_11,
case when m.MP_LEVEL > 1
AND NOT EXISTS
(SELECT 1 FROM trafodion.nanrui_1.C_BILL_RELA r WHERE r.MP_ID
= m.MP_ID) then 1 end RULL_12,
case when m.USAGE_TYPE_CODE = '01'
AND (m.TYPE_CODE = '02' OR m.MP_ATTR_CODE = '02') then 1 end
RULL_13,
case when m.MD_TYPE_CODE = '5'
AND (m.VOLT_CODE NOT IN ('AC02202', 'AC03802') OR
m.WIRING_MODE <> '1') then 1 end RULL_14,
case when m.MD_TYPE_CODE IN ('1', '2')
AND m.VOLT_CODE = 'AC03802' then 1 end RULL_15,
case when c.CONS_SORT_CODE <> '00'
and m.VOLT_CODE = 'AC02202'
AND (m.MD_TYPE_CODE <> '5' OR m.WIRING_MODE <> '1')
and m.MP_ATTR_CODE = '01' then 1 end RULL_16,
case when c.CONS_SORT_CODE <> '00'
and m.WIRING_MODE = '1'
and m.MP_ATTR_CODE = '01'
AND (m.MD_TYPE_CODE <> '5' OR
m.VOLT_CODE NOT IN ('AC02202', 'AC03802')) then 1 end
RULL_17,
case when m.TYPE_CODE = '02'
AND m.USAGE_TYPE_CODE = '01' then 1 end RULL_19,
case when m.TYPE_CODE = '01'
AND m.USAGE_TYPE_CODE >= '02' then 1 end RULL_20,
case when m.USAGE_TYPE_CODE = '01'
AND m.MP_ATTR_CODE = '02' then 1 end RULL_21,
case when m.MEAS_MODE = '3'
AND m.WIRING_MODE = '2' then 1 end RULL_22,
case when m.USAGE_TYPE_CODE IN
('06', '07', '08', '09', '10', '11')
AND m.VOLT_CODE = 'AC02202' then 1 end RULL_23,
case when m.USAGE_TYPE_CODE = '02'
AND m.VOLT_CODE IN ('AC00661',
'AC01101',
'AC02201',
'AC03301',
'AC05001',
'AC07501',
'AC10001') then 1 end RULL_24,
case when m.VOLT_CODE IN ('AC02202', 'AC03802')
AND m.MEAS_MODE = '1' then 1 end RULL_25,
case when m.RUN_DATE > SYSDATE then 1 end RULL_26,
case when m.TYPE_CODE = '02'
AND m.MD_TYPE_CODE = '5' then 1 end RULL_27,
case when m.USAGE_TYPE_CODE IN ('08', '09', '10')
AND m.MD_TYPE_CODE <> '1' then 1 end RULL_28,
case when m.USAGE_TYPE_CODE IN ('02', '03', '04')
AND m.MP_ATTR_CODE = '02'
AND m.MD_TYPE_CODE NOT IN ('3', '4') then 1 end RULL_30
from hive.porc.c_mp m, hive.porc.C_CONS c
where c.CONS_ID = m.CONS_ID
and m.STATUS_CODE <> '04' --排除拆除
and c.STATUS_CODE < '9')
union all
SELECT
'16.电能表相关信息数据准确性' item_detail,
case
when 10.0 - ((nvl(sum(RULL_01), 0) + nvl(sum(RULL_02), 0) +
nvl(sum(RULL_03), 0) + nvl(sum(RULL_04), 0) +
nvl(sum(RULL_05), 0)) * 0.2 / (1000.0 * 5)) < 0 then
0
else
10.0 - ((nvl(sum(RULL_01), 0) + nvl(sum(RULL_02), 0) +
nvl(sum(RULL_03), 0) + nvl(sum(RULL_04), 0) +
nvl(sum(RULL_05), 0)) * 0.2 / (1000.0 * 5))
end score_detail,
'RULL_01:' || nvl(sum(RULL_01), 0) || chr(10) || ',RULL_02:' ||
nvl(sum(RULL_02), 0) || chr(10) || ',RULL_03:' ||
nvl(sum(RULL_03), 0) || chr(10) || ',RULL_04:' ||
nvl(sum(RULL_04), 0) || chr(10) || ',RULL_05:' ||
nvl(sum(RULL_05), 0) data_detai
FROM (select case when m.STATUS_CODE = '02'
and exists
(select 1
from hive.porc.D_METER d
WHERE t.METER_ID = d.METER_ID
AND d.CUR_STATUS_CODE = '012'
AND t.INST_DATE < d.LATEST_CHK_DATE) then 1 end
RULL_01,
case when exists
(select 1
from hive.porc.D_METER d
WHERE t.METER_ID = d.METER_ID
AND NOT EXISTS
(SELECT 1
FROM trafodion.nanrui_1.C_Meter_READ a
WHERE a.METER_ID = t.METER_ID
AND a.MR_DIGIT = d.METER_DIGITS)) then 1 end
RULL_02,
case when exists (select 1
from hive.porc.D_METER d
WHERE t.METER_ID = d.METER_ID
AND t.T_FACTOR > 1
AND d.SELF_FACTOR = 1
AND NOT EXISTS
(SELECT 1
FROM trafodion.nanrui_1.C_IT_RUN i,
trafodion.nanrui_1.c_mp_IT_RELA g
WHERE i.IT_ID = g.IT_ID
AND G.MP_ID = M.MP_ID)) then 1 end RULL_03,
case when exists (SELECT 1
FROM trafodion.nanrui_1.C_IT_RUN i,
trafodion.nanrui_1.c_mp_IT_RELA y
WHERE i.IT_ID = y.IT_ID
AND y.MP_ID = m.MP_ID
AND t.T_FACTOR = 1) then 1 end RULL_04,
case when exists (select 1
from hive.porc.D_METER d
WHERE t.METER_ID = d.METER_ID
AND NOT EXISTS
(SELECT 1
FROM hive.porc.c_mp i,
trafodion.nanrui_1.C_Meter_MP_RELA y
WHERE i.MP_ID = y.MP_ID
AND y.METER_ID = t.METER_ID)) then 1 end
RULL_05
from hive.porc.C_Meter t, hive.porc.c_mp m,
trafodion.nanrui_1.C_Meter_MP_RELA r, hive.porc.C_CONS c
where c.CONS_ID = m.CONS_ID
and m.MP_ID = r.MP_ID
and r.METER_ID = t.METER_ID
and m.STATUS_CODE <> '04' --排除拆除
and c.STATUS_CODE < '9')
union all
SELECT
'17.互感器相关信息数据准确性' item_detail,
case
when 10.0 - ((nvl(sum(RULL_01), 0) + nvl(sum(RULL_02), 0) +
nvl(sum(RULL_03), 0) + nvl(sum(RULL_04), 0) +
nvl(sum(RULL_05), 0) + nvl(sum(RULL_06), 0)) * 0.2 /
(1000.0 * 6)) < 0 then
0
else
10.0 -
((nvl(sum(RULL_01), 0) + nvl(sum(RULL_02), 0) +
nvl(sum(RULL_03), 0) + nvl(sum(RULL_04), 0) +
nvl(sum(RULL_05), 0) + nvl(sum(RULL_06), 0)) * 0.2 / (1000.0 * 6))
end score_detail,
'RULL_01:' || nvl(sum(RULL_01), 0) || chr(10) || ',RULL_02:' ||
nvl(sum(RULL_02), 0) || chr(10) || ',RULL_03:' ||
nvl(sum(RULL_03), 0) || chr(10) || ',RULL_04:' ||
nvl(sum(RULL_04), 0) || chr(10) || ',RULL_05:' ||
nvl(sum(RULL_05), 0) || chr(10) || ',RULL_06:' ||
nvl(sum(RULL_06), 0) data_detai
from (select case when m.VOLT_CODE = 'AC02202'
AND EXISTS
(SELECT 1
FROM trafodion.nanrui_1.C_IT_RUN a,
trafodion.nanrui_1.c_mp_IT_RELA b
WHERE a.IT_ID = b.IT_ID
AND b.MP_ID = m.MP_ID
AND a.SORT_CODE in ('02', '04')) then 1 end RULL_01,
case when m.VOLT_CODE IN ('AC02202', 'AC03802')
AND EXISTS (SELECT 1
FROM trafodion.nanrui_1.C_IT_RUN a,
trafodion.nanrui_1.c_mp_IT_RELA b
WHERE a.IT_ID = b.IT_ID
AND b.MP_ID = m.MP_ID
AND a.SORT_CODE >= '04') then 1 end RULL_02,
case when m.WIRING_MODE = '2'
AND NOT EXISTS
(SELECT 1
FROM trafodion.nanrui_1.C_IT_RUN a,
trafodion.nanrui_1.c_mp_IT_RELA b
WHERE a.IT_ID = b.IT_ID
AND b.MP_ID = m.MP_ID
AND a.SORT_CODE >= '03')
AND (((SELECT COUNT(1)
FROM trafodion.nanrui_1.C_IT_RUN x,
trafodion.nanrui_1.c_mp_IT_RELA y
WHERE x.IT_ID = y.IT_ID
AND y.MP_ID = m.MP_ID
AND x.SORT_CODE = '02') != 2) OR
(SELECT COUNT(1)
FROM trafodion.nanrui_1.C_IT_RUN u,
trafodion.nanrui_1.c_mp_IT_RELA v
WHERE u.IT_ID = v.IT_ID
AND v.MP_ID = m.MP_ID
AND u.SORT_CODE = '02') != 3)then 1 end RULL_03,
case when m.VOLT_CODE IN ('AC10001',
'AC07501',
'AC05001',
'AC03301',
'AC02201',
'AC01101')
AND m.WIRING_MODE = '3'
AND EXISTS
(SELECT 1
FROM trafodion.nanrui_1.C_IT_RUN h,
trafodion.nanrui_1.c_mp_IT_RELA j
WHERE h.IT_ID = j.IT_ID
AND j.MP_ID = m.MP_ID
AND h.SORT_CODE > '02')
AND (((SELECT COUNT(1)
FROM trafodion.nanrui_1.C_IT_RUN k,
trafodion.nanrui_1.c_mp_IT_RELA l
WHERE k.IT_ID = l.IT_ID
AND l.MP_ID = m.MP_ID
AND k.SORT_CODE = '01') != 3) OR
((SELECT COUNT(1)
FROM trafodion.nanrui_1.C_IT_RUN o,
trafodion.nanrui_1.c_mp_IT_RELA n
WHERE o.IT_ID = n.IT_ID
AND n.MP_ID = m.MP_ID
AND o.SORT_CODE = '01') != 6) OR
((SELECT COUNT(1)
FROM trafodion.nanrui_1.C_IT_RUN x,
trafodion.nanrui_1.c_mp_IT_RELA y
WHERE x.IT_ID = y.IT_ID
AND y.MP_ID = m.MP_ID
AND x.SORT_CODE = '02') != 3) OR
((SELECT COUNT(1)
FROM trafodion.nanrui_1.C_IT_RUN x,
trafodion.nanrui_1.c_mp_IT_RELA y
WHERE x.IT_ID = y.IT_ID
AND y.MP_ID = m.MP_ID
AND x.SORT_CODE = '02') != 6)) then 1 end RULL_04,
case when m.TYPE_CODE = '02'
AND m.VOLT_CODE IN ('AC05001', 'AC03301', 'AC02201')
--20130403 old is TYPE_CODE
AND ((SELECT MOD(COUNT(1), 3)
FROM trafodion.nanrui_1.C_IT_RUN a,
trafodion.nanrui_1.c_mp_IT_RELA b
WHERE a.IT_ID = b.IT_ID
AND b.MP_ID = m.MP_ID
AND a.SORT_CODE = '03') > 0 OR
(SELECT MOD(COUNT(1), 3)
FROM trafodion.nanrui_1.C_IT_RUN f,
trafodion.nanrui_1.c_mp_IT_RELA g
WHERE f.IT_ID = g.IT_ID
AND g.MP_ID = m.MP_ID
AND f.SORT_CODE = '02') > 0) then 1 end RULL_05,
case when m.WIRING_MODE = '2'
AND c.VOLT_CODE IN
('AC05001', 'AC03301', 'AC02201', 'AC01101')
AND EXISTS (SELECT 1
FROM trafodion.nanrui_1.C_IT_RUN a,
trafodion.nanrui_1.c_mp_IT_RELA b
WHERE a.IT_ID = b.IT_ID
AND b.MP_ID = m.MP_ID
AND a.SORT_CODE = '04')then 1 end RULL_06
from trafodion.nanrui_1.C_IT_RUN t, hive.porc.c_mp m,
trafodion.nanrui_1.c_mp_IT_RELA r, hive.porc.C_CONS c
where c.CONS_ID = m.CONS_ID
and m.MP_ID = r.MP_ID
and r.IT_ID = t.IT_ID
and m.STATUS_CODE <> '04' --排除拆除
and c.STATUS_CODE < '9'));
> compile failed with 3 union alls
> --------------------------------
>
> Key: TRAFODION-2444
> URL: https://issues.apache.org/jira/browse/TRAFODION-2444
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: Joshua Liu
> Assignee: Suresh Subbiah
> Attachments: 三个union all编译出错，单独编译都能通过.msg
>
>
> the sql is like:
> ----------
> query1
> union all
> query 2
> union all
> query3
> ----------
> it compiled failed
> but query1&2&3 can compile successfully respectively.
> see attachment for more details...
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
