0

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;

enter image description here

5
  • Just stick the STILL_REQUIRED column code in the WHERE clause, with !=0 after...
    – Philᵀᴹ
    Dec 13, 2018 at 16:47
  • Hi, Thanks for the reply but I cant get it working.. please can you copy the text in? Thanks
    – Simon
    Dec 13, 2018 at 17:04
  • Add HAVING still_required != 0 before ORDER BY.
    – Akina
    Dec 13, 2018 at 19:13
  • Thanks, but I get an error as below:
    – Simon
    Dec 14, 2018 at 11:51
  • Invalid token Engine Code : 335544569 Engine Message : Dynamic SQL Error SQL error code = -104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
    – Simon
    Dec 14, 2018 at 11:51

2 Answers 2

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
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.