Hi,

> > 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.

Yes, but it needs a big change to the optimizer.

> > 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)

(it should be Afdeling.Afdelingsnummer || '' in this case).
That will change the order also, but it was important that Varer_Detail uses
twice it's primary index.
You probably didn't have that info, so you couldn't know.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info