I’ve this error ouput:
ERROR: syntax error at or near "FROM"
LINE 14: FROM dat.unit du
SELECT
(SELECT extract (epoch
FROM
(
SELECT avg_freq
FROM tlm.data_qa_tele_freq(du.id, null, now()::timestamp - interval '0.5 day', now()::timestamp)
)
))AS "ASDF",
(case when "ASDF" > 30 )
FROM dat.unit du
The sentence:
SELECT
(SELECT extract (epoch
FROM
(
SELECT avg_freq
FROM tlm.data_qa_tele_freq(du.id, null, now()::timestamp - interval '0.5 day', now()::timestamp)
)
))AS "ASDF"
FROM dat.unit du
returns this coloum:
As you can see it returns null if there is not data and an real number. What I must get is the values which are bigger than 30.
>Solution :
Instead of the CASE statement, you should use a WHERE clause here.
WITH cte AS (
SELECT
(SELECT EXTRACT(epoch FROM
(SELECT avg_freq
FROM tlm.data_qa_tele_freq(du.id,
null,
now()::timestamp - interval '0.5 day',
now()::timestamp)
)
)) AS "ASDF",
FROM dat.unit du
)
SELECT *
FROM cte
WHERE "ASDF" > 30
If the "ASDF" field is a string instead of a integer-like value, you need to use the typecasting operation:
WHERE "ASDF"::INTEGER > 30
Also I’d recommend to check the CASE full syntax on the official documentation.