80 lines
3.0 KiB
Plaintext
80 lines
3.0 KiB
Plaintext
drop function if exists mmt_ods.fn_update_NA();
|
|
create or replace function mmt_ods.fn_update_NA()
|
|
returns void AS $$
|
|
/***********
|
|
select mmt_ods.fn_update_NA()
|
|
********/
|
|
declare
|
|
__n integer:=0;
|
|
__na_sql text;
|
|
__space_sql text;
|
|
__div_sql text;
|
|
__ref_sql text;
|
|
__hyphen_sql text;
|
|
__not_applicable text;
|
|
__NR text;
|
|
begin
|
|
loop
|
|
exit when __n=53;
|
|
__n := __n+1;
|
|
__na_sql := 'update mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
|
|
where '||concat('column',__n)||'=''NA''';
|
|
__space_sql := 'update mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
|
|
where '||concat('column',__n)||'=''''';
|
|
__div_sql := 'update mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
|
|
where '||concat('column',__n)||'=''#REF!''';
|
|
__ref_sql := 'update mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
|
|
where '||concat('column',__n)||'=''#DIV/0!''';
|
|
__hyphen_sql := 'update mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
|
|
where '||concat('column',__n)||'=''-''';
|
|
__not_applicable := 'update mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
|
|
where '||concat('column',__n)||'=''Not Applicable ''';
|
|
__NR := 'update mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
|
|
where '||concat('column',__n)||'=''NR''';
|
|
execute __na_sql;
|
|
execute __space_sql;
|
|
execute __div_sql;
|
|
execute __ref_sql;
|
|
execute __hyphen_sql;
|
|
execute __not_applicable;
|
|
execute __NR;
|
|
end loop;
|
|
end;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
|
|
|
|
drop function if exists mmt_staging2.fn_jobctrl_block_begin;
|
|
CREATE OR REPLACE FUNCTION mmt_staging2.fn_jobctrl_block_begin(p_client_id int,p_function_id int,p_file_syspk int,p_block_table_name text,p_file_format text,p_sheet_mnemonic text,p_block_seq int)
|
|
RETURNS void AS $$
|
|
begin
|
|
|
|
/*************************
|
|
select mmt_staging2.fn_jobctrl_block_begin(1,2,255,'block1','file','sheet',1)
|
|
***********************/
|
|
|
|
execute 'insert into mmt_ods.fw_jobctrl_file_sheet_block_run_schedule
|
|
(client_id,function_id,file_syspk,block_table_name,file_format,sheet_mnemonic,begin_status,block_table_load_seq,start_time)
|
|
select '||p_client_id||', '||p_function_id||', '''||p_file_syspk||''','''||p_block_table_name||''','''||p_file_format||''','''||p_sheet_mnemonic||''',1,'||p_block_seq||',current_timestamp';
|
|
end
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
drop function if exists mmt_staging2.fn_jobctrl_block_end;
|
|
CREATE OR REPLACE FUNCTION mmt_staging2.fn_jobctrl_block_end(p_file_syspk int,p_block_table_name text)
|
|
RETURNS void AS $$
|
|
begin
|
|
|
|
/*********************************
|
|
select mmt_staging2.fn_jobctrl_block_end(255,'block1')
|
|
*********************************/
|
|
|
|
execute 'update mmt_ods.fw_jobctrl_file_sheet_block_run_schedule set end_status=1 , end_time=current_timestamp
|
|
where file_syspk='''||p_file_syspk||'''and block_table_name='''||p_block_table_name||'''';
|
|
|
|
end
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|