371 lines
15 KiB
Plaintext
371 lines
15 KiB
Plaintext
drop table mmt_staging2.test_output_BUDNI;
|
|
|
|
create table mmt_staging2.test_output_BUDNI
|
|
(
|
|
sno serial,
|
|
file_syspk int,
|
|
file_format text,
|
|
sheet_mnemonic text,
|
|
table_name text,
|
|
row_count int
|
|
);
|
|
|
|
drop function if exists mmt_staging2.fn_test_output_BUDNI;
|
|
CREATE OR REPLACE FUNCTION mmt_staging2.fn_test_output_BUDNI(p_file_syspk int)
|
|
RETURNS table
|
|
(
|
|
sno int,
|
|
file_syspk int,
|
|
file_format text,
|
|
sheet_mnemonic text,
|
|
table_name text,
|
|
row_count int
|
|
)
|
|
|
|
as $$
|
|
declare __file_syspk int :=p_file_syspk;
|
|
begin
|
|
|
|
|
|
truncate table mmt_staging2.test_output_BUDNI;
|
|
|
|
execute 'insert into mmt_staging2.test_output_BUDNI
|
|
(file_syspk,file_format,sheet_mnemonic,table_name,row_count)
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_ARC'',''test_instance'',count(*)
|
|
from mmt_ods.test_instance
|
|
where test_file_ref_no='||p_file_syspk||' and test_file_format =''BUDNI''
|
|
and test_file_sheet_format =''BUDNI_ARC''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_ARC'',''test_instance_engine_info'',count(*)
|
|
from mmt_ods.test_instance_engine_info
|
|
where test_file_ref_no='||p_file_syspk||' and test_file_format =''BUDNI''
|
|
and test_file_sheet_format =''BUDNI_ARC''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_ARC'',''test_instance_tractor_info'',count(*)
|
|
from mmt_ods.test_instance_tractor_info
|
|
where test_file_ref_no='||p_file_syspk||' and test_file_format =''BUDNI''
|
|
and test_file_sheet_format =''BUDNI_ARC''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_ARC'',''budni_air_cleaner_perf_results'',count(*)
|
|
from mmt_ods.budni_air_cleaner_perf_results
|
|
where test_file_ref_no='||p_file_syspk||' and test_file_format =''BUDNI''
|
|
and test_file_sheet_format =''BUDNI_ARC''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_ARC'',''budni_test_observations'',count(*)
|
|
from mmt_ods.budni_test_observations
|
|
where test_file_ref_no='||p_file_syspk||' and test_file_format =''BUDNI''
|
|
and test_file_sheet_format =''BUDNI_ARC''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_BRK'',''test_instance'',count(*)
|
|
from mmt_ods.test_instance
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_BRK''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_BRK'',''test_instance_engine_info'',count(*)
|
|
from mmt_ods.test_instance_engine_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_BRK''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_BRK'',''budni_brake_perf_parking_brake_test_results'',count(*)
|
|
from mmt_ods.budni_brake_perf_parking_brake_test_results
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_BRK''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_BRK'',''budni_brake_perf_service_brake_test_results'',count(*)
|
|
from mmt_ods.budni_brake_perf_service_brake_test_results
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_BRK''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_BRK'',''budni_test_observations'',count(*)
|
|
from mmt_ods.budni_test_observations
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_BRK''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_DBP'',''test_instance'',count(*)
|
|
from mmt_ods.test_instance
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_DBP''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_DBP'',''test_instance_engine_info'',count(*)
|
|
from mmt_ods.test_instance_engine_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_DBP''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_DBP'',''test_instance_tractor_info'',count(*)
|
|
from mmt_ods.test_instance_tractor_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_DBP''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_DBP'',''budni_drawbar_perf_results'',count(*)
|
|
from mmt_ods.budni_drawbar_perf_results
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_DBP''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_FLD'',''test_instance'',count(*)
|
|
from mmt_ods.test_instance
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_FLD''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_FLD'',''test_instance_engine_info'',count(*)
|
|
from mmt_ods.test_instance_engine_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_FLD''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_FLD'',''test_instance_tractor_info'',count(*)
|
|
from mmt_ods.test_instance_tractor_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_FLD''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_FLD'',''test_instance_implement_info'',count(*)
|
|
from mmt_ods.test_instance_implement_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_FLD''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_FLD'',''budni_field_perf_results'',count(*)
|
|
from mmt_ods.budni_field_perf_results
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_FLD''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_FLD'',''budni_test_observations'',count(*)
|
|
from mmt_ods.budni_test_observations
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_FLD''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''test_instance'',count(*)
|
|
from mmt_ods.test_instance
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''test_instance_engine_info'',count(*)
|
|
from mmt_ods.test_instance_engine_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''test_instance_tractor_info'',count(*)
|
|
from mmt_ods.test_instance_tractor_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''test_instance_tractor_info_misc'',count(*)
|
|
from mmt_ods.test_instance_tractor_info_misc
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''budni_hydraulic_maint_of_lift_load_results'',count(*)
|
|
from mmt_ods.budni_hydraulic_maint_of_lift_load_results
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''budni_hydraulic_power_test_results'',count(*)
|
|
from mmt_ods.budni_hydraulic_power_test_results
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''budni_hydraulic_lifting_capacity_test_results'',count(*)
|
|
from mmt_ods.budni_hydraulic_lifting_capacity_test_results
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''budni_test_observations'',count(*)
|
|
from mmt_ods.test_instance_engine_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HLG'',''test_instance'',count(*)
|
|
from mmt_ods.test_instance
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HLG''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HLG'',''test_instance_engine_info'',count(*)
|
|
from mmt_ods.test_instance_engine_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HLG''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HLG'',''test_instance_tractor_info'',count(*)
|
|
from mmt_ods.test_instance_tractor_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HLG''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HLG'',''budni_haulage_perf_results'',count(*)
|
|
from mmt_ods.budni_haulage_perf_results
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HLG''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_HLG'',''budni_test_observations'',count(*)
|
|
from mmt_ods.budni_test_observations
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HLG''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_LCG'',''test_instance'',count(*)
|
|
from mmt_ods.test_instance
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_LCG''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_LCG'',''test_instance_engine_info'',count(*)
|
|
from mmt_ods.test_instance_engine_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_LCG''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_LCG'',''test_instance_tractor_info'',count(*)
|
|
from mmt_ods.test_instance_tractor_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_LCG''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_LCG'',''budni_centre_of_gravity_location'',count(*)
|
|
from mmt_ods.budni_centre_of_gravity_location
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_LCG''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_NMT'',''test_instance'',count(*)
|
|
from mmt_ods.test_instance
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_NMT''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_NMT'',''test_instance_engine_info'',count(*)
|
|
from mmt_ods.test_instance_engine_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_NMT''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_NMT'',''test_instance_tractor_info'',count(*)
|
|
from mmt_ods.test_instance_tractor_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_NMT''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_NMT'',''budni_noise_atmospheric_conditions'',count(*)
|
|
from mmt_ods.budni_noise_atmospheric_conditions
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_NMT''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_NMT'',''budni_noise_measurement_results'',count(*)
|
|
from mmt_ods.budni_noise_measurement_results
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_NMT''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_NMT'',''budni_test_observations'',count(*)
|
|
from mmt_ods.budni_test_observations
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_NMT''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_PTO'',''test_instance'',count(*)
|
|
from mmt_ods.test_instance
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_PTO''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_PTO'',''test_instance_engine_info'',count(*)
|
|
from mmt_ods.test_instance_engine_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_PTO''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_PTO'',''test_instance_tractor_info'',count(*)
|
|
from mmt_ods.test_instance_tractor_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_PTO''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_PTO'',''budni_pto_perf_results_summary'',count(*)
|
|
from mmt_ods.budni_pto_perf_results_summary
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_PTO''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_PTO'',''budni_pto_perf_results_details'',count(*)
|
|
from mmt_ods.budni_pto_perf_results_details
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_PTO''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_PTO'',''budni_test_observations '',count(*)
|
|
from mmt_ods.budni_test_observations
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_PTO''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_VMT'',''test_instance'',count(*)
|
|
from mmt_ods.test_instance
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_VMT''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_VMT'',''test_instance_engine_info'',count(*)
|
|
from mmt_ods.test_instance_engine_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_VMT''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_VMT'',''test_instance_tractor_info'',count(*)
|
|
from mmt_ods.test_instance_tractor_info
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_VMT''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_VMT'',''budni_vibration_measurement'',count(*)
|
|
from mmt_ods.budni_vibration_measurement
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_VMT''
|
|
group by test_file_ref_no
|
|
union
|
|
select test_file_ref_no,''BUDNI'',''BUDNI_VMT'',''budni_test_observations'',count(*)
|
|
from mmt_ods.budni_test_observations
|
|
where test_file_ref_no='||p_file_syspk||'
|
|
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_VMT''
|
|
group by test_file_ref_no';
|
|
return query
|
|
|
|
select * from mmt_staging2.test_output_BUDNI;
|
|
|
|
end
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|