On 7/19/22 03:38, Achilleas Mantzios wrote:
I reformatted queries to see thing better.
-- 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
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;
So the above fails. In your title when you say there is no infinity that
means the cept.value, ceptl.min_alarm or ceptl.max_alarm fields do not
have any '-infinity' or 'infinity' values, correct?
-- 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
WITH bar AS (
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_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;
This version succeeds, correct? So breaking the range construction into
one step and the @> test into another works. Though I am not sure why
<range> @> changed to NOT <range> @>?
--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
