oracle - Trigger is giving PL/SQL statement ignored error -
i have trigger , have modified using case statements it's giving errors:
create or replace trigger trg_stg_timeattendance after insert or update on co_conf_em_tm_enr referencing new new each row declare pragma autonomous_transaction; --variable declaration stg_pos_ta - as400 l_induction_fl char(1) := 'n'; l_error_code varchar2(10); l_error_messg varchar2(100); --variable declaration stg_ta_payroll - lotus notes v_ta_date number(6) := null; v_time_in number(4) := null; v_time_out number(4) := null; v_mgr_ovrd char(1) := 'n'; v_area varchar2(20) := null; v_assc_name varchar2(60) := null; v_misc_wages_desc varchar2(30) := null; v_shrt_str_name varchar2(50) := null; v_session_no number(2) := 0; v_oim_enabled char(1); begin ----merging stg_pos_ta - as400 - start begin select decode(nvl(commission_percentage, 0), 0, 'y', 'n') l_induction_fl ebiz_user_cache employee_id = :new.id_em; exception when no_data_found l_induction_fl := 'n'; end; if (:new.fl_conf = '1') merge stg_pos_ta using (select :new.id_tm_enr id_tm_enr, :new.id_em id_em, :new.id_mgr id_mgr, to_number(:new.id_str_rt) id_str_rt, :new.ts_em_tm_in ts_em_tm_in, :new.ts_em_tm_out ts_em_tm_out, :new.hrs_str hrs_str, :new.other_hrs_str other_hrs_str, :new.ty_tm ty_tm, :new.fl_conf fl_conf, :new.cd_mgr_rsn cd_mgr_rsn, :new.fl_mod fl_mod, :new.fl_del fl_del, l_induction_fl emp_induction_fl, :new.ts_crt_rcrd ts_crt_rcrd, :new.ts_mdf_rcrd ts_mdf_rcrd dual) stg on (time_ent_id = :new.id_tm_enr , to_number(str_id) = to_number(:new.id_str_rt)) when matched update set emp_id = stg.id_em, mgr_id = stg.id_mgr, emp_tm_in = stg.ts_em_tm_in, emp_tm_out = stg.ts_em_tm_out, tot_hrs = stg.hrs_str, tot_hrs_othrs = stg.other_hrs_str, time_typ = stg.ty_tm, confirm_mgr_fl = stg.fl_conf, mgr_rsn_cd = stg.cd_mgr_rsn, tm_modified_fl = stg.fl_mod, tm_deleted_fl = stg.fl_del, emp_induction_fl = stg.emp_induction_fl, ts_mdf_rcrd = stg.ts_mdf_rcrd, stg_osb_status = 0, stg_osb_process_date = null when not matched insert (time_ent_id, emp_id, mgr_id, str_id, emp_tm_in, emp_tm_out, tot_hrs, tot_hrs_othrs, time_typ, confirm_mgr_fl, mgr_rsn_cd, tm_modified_fl, tm_deleted_fl, emp_induction_fl, ts_crt_rcrd, ts_mdf_rcrd, stg_event_id, stg_osb_status, stg_osb_error_message, stg_load_date, stg_osb_process_date) values (stg.id_tm_enr, stg.id_em, stg.id_mgr, to_number(stg.id_str_rt), stg.ts_em_tm_in, stg.ts_em_tm_out, stg.hrs_str, stg.other_hrs_str, stg.ty_tm, stg.fl_conf, stg.cd_mgr_rsn, stg.fl_mod, stg.fl_del, l_induction_fl, stg.ts_crt_rcrd, stg.ts_mdf_rcrd, seq_stg_event_id.nextval, 0, null, sysdate, null); commit; ----merging stg_pos_ta - as400 - end ----changes merging stg_ta_payroll - lotus notes - start --manager override flag if ((:new.ty_tm = '0' , :new.id_mgr not null) or (:new.ty_tm != '0')) v_mgr_ovrd := 'y'; end if; --session number if (:new.ty_tm = '0') select count(*) v_session_no co_conf_em_tm_enr id_em = :new.id_em , trunc(ts_em_tm_in) = trunc(:new.ts_em_tm_in) , ty_tm = '0' , fl_conf = '1'; select v_session_no + 1 v_session_no dual; else select count(*) v_session_no co_conf_em_tm_enr id_em = :new.id_em , trunc(ts_em_tm_in) = trunc(:new.ts_em_tm_in) , ty_tm != '0' , fl_conf = '1'; select v_session_no + 50 v_session_no dual; end if; --area code store begin select nvl(id_node_prnt, 0) v_area ebiz_stg_store_hierarchy_cache to_number(id_node) = to_number(:new.id_str_rt); exception when no_data_found v_area := null; end; --associate name begin select lkup_val v_oim_enabled sh_ebiz_lookup lkup_cd = 'oim_enabled' , lkup_type = 'system_config'; if (v_oim_enabled = 'y') select first_name || ' ' || last_name v_assc_name ebiz_user_cache employee_id = :new.id_em; else select fn_em || ' ' || ln_em v_assc_name nbty_store_employee id_em = :new.id_em; end if; exception when no_data_found v_assc_name := null; end; --misc wages description begin select lkup_val v_misc_wages_desc sh_ebiz_lookup lkup_type = 'payroll_misc_wages' , lkup_cd = :new.ty_tm; exception when no_data_found v_misc_wages_desc := null; end; --short store name begin select trim(substr(nm_level, 1, instr(nm_level, '(') - 1)) v_shrt_str_name ebiz_stg_store_hierarchy_cache to_number(id_node) = to_number(:new.id_str_rt); exception when no_data_found v_shrt_str_name := null; end; merge stg_ta_payroll using (select :new.id_tm_enr id_tm_enr, to_number(:new.id_str_rt) id_str_rt, :new.id_em id_em, nvl(to_number(to_char(:new.ts_em_tm_in, 'yymmdd')), 0) v_ta_date, v_session_no v_session_no, :new.ts_em_tm_in ts_em_tm_in, :new.ts_em_tm_out ts_em_tm_out, (case when :new.ty_tm != '0' :new.ts_em_tm_in = '' else nvl(to_char(:new.ts_em_tm_in, 'hh24mi'), '0000') end) v_time_in, (case when :new.ty_tm != '0' :new.ts_em_tm_out = '' else nvl(to_char(:new.ts_em_tm_out, 'hh24mi'), '0000') end) v_time_out, v_mgr_ovrd v_mgr_ovrd, :new.id_mgr id_mgr, decode(:new.ty_tm, 'xx', '9', :new.ty_tm) ty_tm, :new.other_hrs_str other_hrs_str, v_area v_area, v_assc_name v_assc_name, v_misc_wages_desc v_misc_wages_desc, v_shrt_str_name v_shrt_str_name, (:new.hrs_str + :new.other_hrs_str) tot_hrs dual) stg on (time_ent_id = stg.id_tm_enr , to_number(str_id) = to_number(stg.id_str_rt)) when matched update set status = null, emp_id = stg.id_em, ta_date = stg.v_ta_date, --session_no = stg.v_session_no, time_in = stg.v_time_in, time_out = stg.v_time_out, mgr_override = stg.v_mgr_ovrd, mgr_emp_no = stg.id_mgr, misc_wages_code = stg.ty_tm, misc_wages_hrs = stg.other_hrs_str, area = stg.v_area, associate_name = stg.v_assc_name, misc_wages_desc = stg.v_misc_wages_desc, shrt_str_name = stg.v_shrt_str_name, hrs = stg.tot_hrs, stg_osb_process_date = null when not matched insert (time_ent_id, status, str_id, emp_id, ta_date, session_no, time_in, time_out, mgr_override, mgr_emp_no, misc_wages_code, misc_wages_hrs, area, associate_name, misc_wages_desc, shrt_str_name, hrs, stg_load_date, stg_event_id, stg_osb_status) values (stg.id_tm_enr, null, stg.id_str_rt, stg.id_em, stg.v_ta_date, stg.v_session_no, stg.v_time_in, stg.v_time_out, stg.v_mgr_ovrd, stg.id_mgr, stg.ty_tm, stg.other_hrs_str, stg.v_area, stg.v_assc_name, stg.v_misc_wages_desc, stg.v_shrt_str_name, stg.tot_hrs, sysdate, seq_stg_event_id.nextval, 0); commit; end if; ----changes merging stg_ta_payroll - lotus notes - end exception when others update co_conf_em_tm_enr set id_tlog_btch = 1, btch_msg_dsc = 'error inserting stg_pos_commission - ' || l_error_code || ' - ' || l_error_messg id_em = :new.id_em , to_char(ts_em_tm_in, 'dd-mm-yyyy') = to_char(:new.ts_em_tm_in, 'dd-mm-yyyy'); commit; end trg_stg_timeattendance; i'm getting error on using keyword shows sql statement ignored , on case statement gives missing keyword error.
the case expressions malformed. let's consider following:
(case when :new.ty_tm != '0' :new.ts_em_tm_in = '' else nvl(to_char(:new.ts_em_tm_in, 'hh24mi'), '0000') end) v_time_in the "when" branch attempts return boolean (which isn't legitimate data type in sql), "else" branch returns character string. that's not allowed - values returned branches in case expression must of same type, or null.
share , enjoy.
edit: , having seen comment replacing case decode - can same results case expression doing following:
case when :new.ty_tm = '0' nvl(to_char(:new.ts_em_tm_in,'hh24mi'),'0000') else null end v_time_in
Comments
Post a Comment