> Set, let me try :)
> Bayu, what FB version do you use?

Firebird 1.0.2, 1.0.3, 1.5 RC8 and Interbase 7.1 give me some wrong
consistence results

> 1. Look at your first subselect:
>
> Select first 1 a1.deptid
> from atdhistory_temp a1
> where a1.empid = :empid_2
> and a1.workdate = ...
>
> it does'nt have order by and will at each run return arbitrary row.
> FB1 does'nt allow order by in subselects at all so such a subselect is
> unusable, while FB1.5 allows for subselects with First 1 and you
> should use it to make you query fully determined.
>
> 2. There is old IB bug in parameters ordering and distinguishing is
> sub-select correlated with main query or not. Due to this bug queries
> which contain parameters in both main select and sub-select never
> worked stable correctly in IB and FB1. Sometimes such a query return
> right result, sometimes not. I can't exactly say what kind of such a
> queries work correctly and what not - stepped on this rake about 8
> years ago and never made it since that time, perhaps someone else can
> be more exact. It is said bug is fixed in FB1.5 but knowing that for
> ages client components libraries such as IBX, IBO, FIBPlus implemented
> different workarounds for this bug, this behaiour for compatibility
> was made switchable by parameter OldParameterOrdering in
> firebird.conf. As I said, personally I can't be more exact on this
> because avoid such a queries by means for example
>
> (Select first 1 a1.deptid
> from atdhistory_temp a1
> where a1.empid = :empid_2
> and a1.workdate =
> (Select max( a2.workdate )
> from atdhistory_temp a2
> where a2.workdate between :mulai and :mulai + 6
> and a2.empid = a1.empid /*Note repeatable usage of :empid_2 is
> eleminated here*/
> )
> Order by something
> )

Followed yours suggestion ( with no repeatable usage of :empid_2 and
without order by clausul ), its WORKS.
For now i will trying to eleminated using repeatable parameter as possible.

It is will be fixed on next release of firebird ?

> Perhaps it is enough to get right result in your case.

Thanks all,

best regards
bayu