dynacom=# select it.id ,cept.value::numeric as val, numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric ,'()') as range from items it, cept_report cept , dynacom.vessels vsl, machdefs md, cept_reportlimits ceptl wh ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND it.vslwhid=vsl.id AND vsl.vslstatus='Acti ve' and md.application = 'Critical Equipment Performance Test' AND cept.systemdate>= (now()-'1 year'::interval ) AND cept.value < -100 ORDER BY 1; id | val | range ----+-----+------- (0 rows)
select it.id ,cept.value::numeric as val, numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric ,'()') as range from items it, cept_report cept , dynacom.vessels vsl, machdefs md, cept_reportlimits ceptl wh ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND it.vslwhid=vsl.id AND vsl.vslstatus='Acti ve' and md.application = 'Critical Equipment Performance Test' AND cept.systemdate>= (now()-'1 year'::interval ) AND cept.value::numeric>'-1' ORDER BY 1; ERROR: cannot convert infinity to numeric -- has no problem testing against infinity select it.id ,cept.value::numeric as val, numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric ,'()') as range from items it, cept_report cept , dynacom.vessels vsl, machdefs md, cept_reportlimits ceptl wh ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND it.vslwhid=vsl.id AND vsl.vslstatus='Acti ve' and md.application = 'Critical Equipment Performance Test' AND cept.systemdate>= (now()-'1 year'::interval ) AND cept.value='inf' ORDER BY 1; id | val | range ----+-----+------- (0 rows) -- still has problem testing the range select it.id ,cept.value::numeric as val, numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric ,'()') as range from items it, cept_report cept , dynacom.vessels vsl, machdefs md, cept_reportlimits ceptl wh ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND it.vslwhid=vsl.id AND vsl.vslstatus='Acti ve' and md.application = 'Critical Equipment Performance Test' AND cept.systemdate>= (now()-'1 year'::interval ) AND numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric,'()') @> cept.value::numeric ORDER BY 1; ERROR: cannot convert infinity to numeric -- no problem if the query goes into its barrier and the test done outside with bar as (select it.id ,cept.value::numeric as val, numrange(ceptl.min_alarm::numeric,ceptl.max_a larm::numeric,'()') as range from items it, cept_report cept , dynacom.vessels vsl, machdefs md, cept_reportli mits ceptl where it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND it.vslwhid=vsl.id AND vsl.vs lstatus='Active' and md.application = 'Critical Equipment Performance Test' AND cept.systemdate>= (now()-'1 ye ar'::interval) ORDER BY 1) select * from bar where NOT range @> val; -- good data here -- Achilleas Mantzios DBA, Analyst, IT Lead IT DEPT Dynacom Tankers Mgmt