--- In firebird-support@yahoogroups.com, "Svein Erling"
<svein.erling.tysvaer@k...> wrote:

> Though hopefully others will jump in at this list and solve your
> problem within a few minutes ;.)
>

Set, let me try :)

Bayu, what FB version do you use?

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
)

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

Best regards,
Alexander.