--- In firebird-support@yahoogroups.com, "Arno Brinkman" wrote:

> Change :
>
> > where
> > vp_parentpos = :PARENT and
> > vp_delete != "T" and
> > vp_posart = 1 and
> > vo_delete != "T" and
> > vo_status in ("2", "4", "9") and
> > vo_art in (2, 3)
>
> to :
>
> where
> vp_parentpos = :PARENT and
> vp_delete != "T" and
> vp_posart = 1 and
> vo_delete != "T" and
> ((vo_status = "2" and vo_art = 2) or
> (vo_status = "4" and vo_art = 2) or
> (vo_status = "9" and vo_art = 2) or
> (vo_status = "2" and vo_art = 3) or
> (vo_status = "4" and vo_art = 3) or
> (vo_status = "9" and vo_art = 3))
>
> Why?
>
> Because now it can use optimal the index on VO_ART, VO_STATUS
> Let us know if this helps.

Hmm, interesting... Any plans to make the optimizer able to do such
conversion automatically? I think I can see it could be difficult when
things gets just a little bit more complex.

Arno then wrote:

> SELECT
> SUM( VARER_DETAIL.ANTALSTK ) ANTALSTK,
> SUM( VARER_DETAIL.BEH_KOSTPRIS ) BEH_KOSTPRIS,
> SUM( VARER_DETAIL.BEH_SALGSPRIS ) SALGSPRIS,
> SUM( VARER_DETAIL.BEH_SALGSPRISMOMS ) SALGSPRISMOMS,
> VARER_DETAIL.AFDELING_ID,
> VARER.LEVERID GRUP2,
> LEVERANDOERER.V509Index GRUP2509,
> Afdeling.Navn,
> Afdeling.AfdInfo
> FROM
> VARER
> JOIN Varer_Detail ON
> ((Varer_Detail.VarePlu_ID = Varer.Plu_Nr || '' and
> Varer_detail.Afdeling_ID = '001') or
> (Varer_Detail.VarePlu_ID = Varer.Plu_Nr || '' and
> Varer_detail.Afdeling_ID = '002'))
> JOIN Afdeling
> on (Afdeling.Afdelingsnummer = Varer_Detail.Afdeling_ID)
> JOIN LEVERANDOERER on (Varer.LeverID=LEVERANDOERER.Navn)
> GROUP BY
> VARER_DETAIL.AFDELING_ID,
> LEVERANDOERER.V509Index,
> VARER.LEVERID,
> Afdeling.Navn,
> Afdeling.AfdInfo

and I thought this one could be solved by simply changing to

Inner Join Afdeling
on (Afdeling.Afdelingsnummer+0 = Varer_Detail.Afdeling_ID)

Set