> the SQL statement that containing the error is
Reformatted by me (it was almost unreadable) and slightly modified (no need to
cast nulls)
> -----------------------------------------------------------------
> 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, null, null, null, null, null, null, null, cast('ÑÕíÏ' as
> varchar(50)),
> cast('Balance' as varchar(50)), SUM(E_TO), SUM(E_FROM),
> SUM(E_TO)-SUM(E_FROM),
> null, MAX(MOV_DATE), MAX(POST_DATE), null, null, null, null
> 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
Aggregate functions cannot be in the WHERE clause, they must be in HAVING.
Moreover, all non-aggregate fields must be in a GROUP BY clause and your query
doesn't even have GROUP BY. There's no way this statement could even parse in
any Firebird or InterBase version.
I'm even a bit uncertain what min(post_date) means in this setting, and to me
it seems like 'min(post_date) > post_date' would always be false.
'min(post_date) > another_date' could normally be replaced by 'and not
exists(select * from table where ... and post_date < another_date')', but I'm
confused both by your use of post_date and that this is a union that involves a
stored procedure.
So please fix your sql and clarify your question.
Set
