Re: Posting from the Informix-list

How about: AND ch.ch_rec_effect_dt <= ? AND NVL(ch.ch_rec_expiry_dt, MDY(1,1,2999)) >= ? AND co.co_rec_effect_dt <= ? AND NVL(co.co_rec_expiry_dt, MDY(1,1,2999)) >= ? AND op.op_rec_effect_dt <= ? AND NVL(op.op_rec_expiry_dt, MDY(1,1,2999)) >= ? -- Regards, Doug Lawry www.douglawry.webhop.org "Deenadhayalan Subramani" <inf_deena@yahoo.com> wrote in message news:cj906q$b1l$1@news.xmission.com... (double spacing and tabs removed) > Any better ways to re write this query. > > SELECT > ch.ch_rec_key, > ch.ch_ctry_hol_lk, > ch.cal_calendar_dt, > co.co_ctry_cd, > op.op_type_cd, > ch.ch_name, > ch.ch_remarks, > ch.ch_rec_effect_dt, > ch.ch_rec_expiry_dt, > ch.ch_chg_timestamp > FROM > country_holiday ch, country co, op_type op > WHERE > ch.op_type_lk = op.op_type_lk AND > ch.co_ctry_lk = co.co_ctry_lk AND > co.co_ctry_cd = ? AND > ch.cal_calendar_dt = ? > AND > ( ( ch.ch_rec_effect_dt <= ? AND > ch.ch_rec_expiry_dt >= ? ) OR > ( ch.ch_rec_effect_dt <= ? AND > ch.ch_rec_expiry_dt is null ) ) > AND > ( ( co.co_rec_effect_dt <= ? AND > co.co_rec_expiry_dt >= ? ) OR > ( co.co_rec_effect_dt <= ? AND > co.co_rec_expiry_dt is null ) ) > AND > ( ( op.op_rec_effect_dt <= ? AND > op.op_rec_expiry_dt >= ? ) OR > ( op.op_rec_effect_dt <= ? AND > op.op_rec_expiry_dt is null ) ) > > Thanks > Deena