SQL firebird dont select rows with 0

firebirdselect

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;

Best Answer

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

Addendum 1

Firebird 3 (since Alpha 1) has now also implemented the old authentication method used by Jaybird, so Jaybird will now also work without using the native library (as long as the legacy authentication is enabled, and the user has been setup with the legacy usermanager).

Addendum 2

Jaybird 3 and higher support the more secure Srp authentication model out of the box, and Jaybird 3.0.4 introduced support for the wire protocol encryption. This means that it can connect to Firebird 3 without additional configuration.

Firebird – Calculate Time Interval Between Two Dates

Relatively green Firebird n00b here w/a fair PostgreSQL background, although specialized GIS work focused mostly to DB design and the sourcing/cleaning/making spatial multi-source data sets (usually of dubious quality) and then querying for map-able results in answer to specific location riddles. Toolbox is light, therefore, on Admin/Optimization/3rd Party Application Setup etc. You may (err, will) see some Firebird specific questions from me in the coming weeks. Thanks in advance! Took a stab at this slightly older Firebird question to get a feel for the new-to-me flavor of date handling and concatenation. Took more tweaking & testing than expected & my dabbling may not be the most efficient/elegant solution but, assuming the existence of column join_date in table employees, this will work:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
THEN datediff(year, join_date, current_date)-1
ELSE datediff(year, join_date, current_date)
END as Y,
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)-1
WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)>=0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)
WHEN datediff(year, join_date, current_date) = datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

The subquery turned conditional on me when it became clear that: a) calculations on year & month don't consider the date as a whole; and b) date subtraction will happily return negative values regardless of the order of events.

Year/Month/Day Example: datediff() returns a value of 1 when retrieving the number of days, months, or years between 31.12.2015 and 1.1.2016.

Negative Value Example: about 69% of my test-date values are less than zero when I calculate the number of days less the number of days contained by whole months using:

SELECT datediff(day, dateadd(month, datediff(month, date_column, current_date), date_column), current_date) as D FROM date_table

We can streamline the subquery if substituting 365 days for one year is acceptable. The trick works with my test data but may trigger pitfalls with a broader range of dates. The simplified query looks like:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
datediff(day, join_date, current_date)/365 as Y,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

Holler if I goofed it or if I employed bad/overwrought logic. Hope it's helpful to someone at some point! Keep a lookout for new Firebird questions (might already have locked in struggle with a JDBC connection string that just will not lose).

Thanks and hello again! --Rob

I never had a plan-- Anonyman

Best Answer

Related Solutions

No luck trying out Firebird 3.0

Addendum 1

Addendum 2

Firebird – Calculate Time Interval Between Two Dates

Related Question