oracle中decode用于where 中
oracle中decode用于where 中 有个例子。查询时有两个参数 开始时间ldt_begin,结束时间ldt_end 当结束日期大于当前时间，某个值取 is null 否则 <ldt_end
操作方法
- 01
select k.patient_id, k.visit_id, k.charge_type, t.name, t.inp_no, k.mailing_address, t.phone_number_home, t.date_of_birth, k.admission_date_time, k.dept_admission_to, k.discharge_date_time, k.dept_discharge_from from pat_visit k, pat_master_index t where t.mailing_address = '本院' and t.patient_id = k.patient_id and k.discharge_date_time >= to_date('2014-01-01', 'yyyy-mm-dd') and (decode(sign(to_date('2014-10-01', 'yyyy-mm-dd') - sysdate), 1, 1, 0) = 1 and k.discharge_date_time is null)unionselect k.patient_id, k.visit_id, k.charge_type, t.name, t.inp_no, k.mailing_address, t.phone_number_home, t.date_of_birth, k.admission_date_time, k.dept_admission_to, k.discharge_date_time, k.dept_discharge_from from pat_visit k, pat_master_index t where t.mailing_address = '本院' and t.patient_id = k.patient_id and k.discharge_date_time >= to_date('2014-01-01', 'yyyy-mm-dd') and decode(sign(to_date('2014-10-01', 'yyyy-mm-dd') - sysdate), 1, 1, 0) = 0 and k.discharge_date_time <= to_date('2014-10-01', 'yyyy-mm-dd'); select (decode(sign(to_date('2014-10-01', 'yyyy-mm-dd') - sysdate),1,1,0) = 1 or k.discharge_date_time is null) and k.discharge_date_time <= to_date('2014-10-01', 'yyyy-mm-dd') from pat_visit k
- 02
select k.patient_id, k.visit_id, k.charge_type, t.name, t.inp_no, k.mailing_address, t.phone_number_home, t.date_of_birth, k.admission_date_time, k.dept_admission_to, k.discharge_date_time, k.dept_discharge_from from pat_visit k, pat_master_index t where t.mailing_address = '本院' and t.patient_id = k.patient_id and k.discharge_date_time >= to_date('2014-01-01', 'yyyy-mm-dd') and (decode(sign(to_date('2014-10-01', 'yyyy-mm-dd') - sysdate), 1, 1, 0) = 1 and k.discharge_date_time is null)
- 03
select k.patient_id, k.visit_id, k.charge_type, t.name, t.inp_no, k.mailing_address, t.phone_number_home, t.date_of_birth, k.admission_date_time, k.dept_admission_to, k.discharge_date_time, k.dept_discharge_from from pat_visit k, pat_master_index t where t.mailing_address = '本院' and t.patient_id = k.patient_id and k.discharge_date_time >= to_date('2014-01-01', 'yyyy-mm-dd') and decode(sign(to_date('2014-10-01', 'yyyy-mm-dd') - sysdate), 1, 1, 0) = 0 and k.discharge_date_time <= to_date('2014-10-01', 'yyyy-mm-dd'); select (decode(sign(to_date('2014-10-01', 'yyyy-mm-dd') - sysdate),1,1,0) = 1 or k.discharge_date_time is null) and k.discharge_date_time <= to_date('2014-10-01', 'yyyy-mm-dd') from pat_visit k