On 19/7/22 20:32, Adrian Klaver wrote:
On 7/19/22 10:26 AM, Achilleas Mantzios wrote:
Thank you Adrian!
Actually thank:
https://sqlformat.darold.net/
Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε:
On 7/19/22 03:38, Achilleas Mantzios wrote:
I reformatted queries to see thing better.
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?
There is infinity in cept.value , just not in this result set.
I got confused and wrongly assumed that since the result set (without the filter in the WHERE clause including cept.value::numeric) did not contain any infinity it should also work with the filter
in the WHERE clause. Apparently a subplan executes this conversion in the WHERE before the other filters. I did not do any analyze to prove this.
Have you tried:
NULLIF(cept.value, 'inf')::numeric
no, cause the CTE version worked. Will keep in mind for similar future problems.
--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt
--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt
