SQL firebird dont select rows with 0

Question

This works fine, except in the results, I don't want the rows where STILL_REQUIRED is 0.

I am using firebird.

Select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE, 
       START_OFFSET_MINS, 
       TOTAL_MINUTES_REQ - 
             (SELECT coalesce(SUM(TABLE_PROCESSTIMES.END_TIME-TABLE_PROCESSTIMES.START_TIME) / 60, 0) 
              FROM TABLE_PROCESSTIMES 
              WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
             ) AS STILL_REQUIRED 
FROM TABLE_PROCESSES 
ORDER BY JOBID, ORDERNUM;

Answer 1

The simplest is to nest it in another select with the appropriate condition:

select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE, START_OFFSET_MINS, STILL_REQUIRED 
from (
    Select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE, 
           START_OFFSET_MINS, 
           TOTAL_MINUTES_REQ - 
                 (SELECT coalesce(SUM(TABLE_PROCESSTIMES.END_TIME-TABLE_PROCESSTIMES.START_TIME) / 60, 0) 
                  FROM TABLE_PROCESSTIMES 
                  WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
                 ) AS STILL_REQUIRED 
    FROM TABLE_PROCESSES 
    ORDER BY JOBID, ORDERNUM
) a
where still_required <> 0

Answer 2

If you move the inline select to the from clause you can add the condition to your where clause without having to nest it in another select.

Select TP.ID, 
       TP.JobID, 
       TP.NAME, 
       TP.ORDERNUM, 
       TP.STARTAFTER, 
       TP.FINISHBEFORE, 
       TP.START_OFFSET_MINS, 
       TPT.STILL_REQUIRED 
FROM TABLE_PROCESSES TP,
     (SELECT TP.TOTAL_MINUTES_REQ - COALESCE(SUM(TPT.END_TIME-TPT.START_TIME) / 60, 0) 
              FROM TABLE_PROCESSES TP,
                   TABLE_PROCESSTIMES TPT
              WHERE TPT.PROCESSID =TP.ID 
              AND   TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
             ) TPT
WHERE TPT.PROCESSID =TP.ID 
AND   TPT. still_required != 0
ORDER BY TP.JOBID, 
         TPORDERNUM;