condition in a sql sentence-CodePudding

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.

CodePudding user response：

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.