Subject Re: [firebird-support] HAVING Clause in Firebird vs. Interbase
Author Helen Borrie
At 03:32 AM 27/11/2007, you wrote:
>When using Interbase, we had the following HAVING clause in a query,
>which is not allowed in Firebird. Any suggestions on how to rework
>this in Firebird? Let me know if you need more info.
>HAVING (sch_StartTime+0.00348) < (f_StripDate(Min(CheckInTime))-1)
> OR (sch_EndTime-0.00348) > (f_StripDate(Max(chk_CheckOutTime))-1)

Well, this is too vague to offer a solution or an explanation but you are breaking one the golden rules for aggregated queries. You should use the WHERE clause for your search criteria. Use the HAVING clause only to filter the aggregations...

Here, e.g.,

count (blah)
from aTable
and (sch_StartTime+0.00348) < (f_StripDate(Min(CheckInTime))-1)
OR (sch_EndTime-0.00348) > (f_StripDate(Max(chk_CheckOutTime))-1)
group by count(blah)
HAVING count(blah) > 1

I haven't even attempted to guess what you're aggregating on, but Firebird won''t let you include any grouping criteria that are not present in the SELECT specification (other than the actual aggregation, of course). Interbase would tolerate all kinds of invalid syntax, allowing unpredictable results. Firebird is a whole lot meaner. And it gets meaner as you ascend through the versions. :-)