the SQL statement that containing the error is
-----------------------------------------------------------------
select L_ID, AC_ID, AC_ENAME
, AC_ANAME , DOCNO, REF,
ENTRY_ID, JV ,
A_DESC , E_DESC ,
DT , CR ,
BLNC_REC , BAL_AC_ID , MOV_DATE
, POST_DATE , CHK ,
US_EX , AC_USER ,
NOTES from ldgr(:AC_ID)
union select 0 ,cast(null as varchar(30)) , cast(null as varchar(50)),
cast(null as varchar(50)), cast(null as varchar(20)), cast(null as
varchar(20)) ,cast(null as integer), cast(null as varchar(10)),cast('ÑÕíÏ' as
varchar(50)) , cast('Balance' as varchar(50)) ,SUM(E_TO) , SUM(E_FROM) ,
SUM(E_TO)-SUM(E_FROM),cast(null as varchar(30)), MAX(MOV_DATE), MAX(POST_DATE),
cast(null as smallint), cast(null as float), cast(null as varchar(15)),
cast(null as varchar(50))
from entry where AC_CR starting with :AC_ID
having min(post_date)>post_date
order by 14, 1
----------------------------------------------------------
how can I replace the HAVING?
I tried to put 'WHERE'
like below:
from entry where AC_CR starting with :AC_ID
and min(post_date)>post_date
but compiler gave me:
cannot use an aggregate functio in a where clause, use HAVING instead.
so is there anyway to get off this ?
--- In [email protected], Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:
>
> >after the update i got a problem with the application.... the error msg is
> >
> >General SQL erroe.
> >Invalid expression in the HAVING clause (nither an aggregate function nor a
> >part of the GROUP BY clause).
> >I think I need to update the the UDF or LIB maybe
> >
> >the application was working great with the FB ver. 1.0 but when I update to
> >2.5 I got this message.
> >any advice?
> >
> >I have to applications (delphi) only one of them is giving the error and the
> >other is great.
>
> Hi Ahmad!
>
> I don't think this error has anything to do with UDFs or similar, it is more
> likely poorly written SQL within the application. Firebird 2.5 is stricter
> regarding the SQL than Firebird 1.0 was. The particular error you observed
> can be experienced when you do refer to a variable neither in the output set
> nor an aggregate function in your HAVING clause. E.g. (using RDB$DATABASE in
> this example, it exists in all Firebird databases, so you can test the SQL on
> your database)
>
> select RDB$DESCRIPTION
> from RDB$DATABASE
> group by RDB$DESCRIPTION
> having RDB$RELATION_ID > 0
>
> This is incorrect SQL (RDB$RELATION_ID is not part of the grouped result,
> since I don't have Fb 1.0 here, I don't know whether that version would have
> allowed this or not) and should be replaced by
>
> select RDB$DESCRIPTION
> from rdb$database
> where RDB$RELATION_ID > 0
> group by RDB$DESCRIPTION
>
> You may discover other differences as well and thorough testing of your
> applications are recommended when upgrading from 1.0 to 2.5 (of course, how
> thorough aren't the same for applications used in nuclear power plants and
> for applications to keep track of birthdays within a family).
>
> HTH,
> Set
>
