At 11:13 AM 05-11-02 -0500, you wrote:

>Thanks Helen,
>
>I don't begin to understand it, but here it is:
>
>/*---
>PREPARE STATEMENT
>TR_HANDLE = 16342000
>STMT_HANDLE = 21905836
>
>Select mc.MenuCategoryID, mi.MenuItemID, mc.CategoryName, mi.ItemCode,
>mi.ItemName, mi.Price, mi.Sales
> , XMENUITEM.RDB$DB_KEY
> >From xMenuCategory mc JOIN xMenuItem mi
>ON (mc.MenuCategoryID = mi.MenuCategoryID)
>and (mc.LocationID = 2)
>and (mi.LocationID = 2)
>ORDER BY mc.CategoryName ASC
> , mi.ItemCode ASC
>
>PLAN SORT (JOIN (MC INDEX (RDB$FOREIGN16),MI INDEX (RDB$FOREIGN17)))

Get into IB_SQL and inspect these two indexes and also inspect the
RDB$PRIMARYnn index that is on xMenuCategory. It seems pretty bizarre that
the optimizer is using a foreign key index for the lefthand side of the
join. I'm suspicious that you might have some indexes stepping on one
another in these structures and confusing the optimizer.

>FIELDS = [ Version 1 SQLd 8 SQLn 59
> XMENUCATEGORY.MENUCATEGORYID = <NIL>
> XMENUITEM.MENUITEMID = <NIL>
> XMENUCATEGORY.CATEGORYNAME = <NIL>
> XMENUITEM.ITEMCODE = <NIL>
> XMENUITEM.ITEMNAME = <NIL>
> XMENUITEM.PRICE = <NIL>
> XMENUITEM.SALES = <NIL>
> XMENUITEM.DB_KEY = <NIL> ]
>----*/
>/*---
>PREPARE STATEMENT
>TR_HANDLE = 16342000
>STMT_HANDLE = 21905836
>
>Select mc.MenuCategoryID, mi.MenuItemID, mc.CategoryName, mi.ItemCode,
>mi.ItemName, mi.Price, mi.Sales
> , XMENUITEM.RDB$DB_KEY
> >From xMenuCategory mc JOIN xMenuItem mi
>ON (mc.MenuCategoryID = mi.MenuCategoryID)
>and (mc.LocationID = 2)
>and (mi.LocationID = 2)
>WHERE (LocationID = 2)
>ORDER BY mc.CategoryName ASC
> , mi.ItemCode ASC

Your SQL isn't inputting the right statement to get the LOCATIONID key
segment lined up for the join. You are trying to force the engine to treat
locationid as a row selector, when what you need is to have it recognised
as a join criterion.

Try this one:

Select mc.MenuCategoryID,
mi.MenuItemID,
mc.CategoryName,
mi.ItemCode,
mi.ItemName,
mi.Price,
mi.Sales
From xMenuCategory mc JOIN xMenuItem mi
ON (mc.MenuCategoryID = mi.MenuCategoryID)
and (mc.LocationID = mi.LocationID) /* join criterion */

where mc.LocationID = :LocationID /* row selector, if you need one */
ORDER BY mc.CategoryName, mi.ItemCode

AND DEFINE YOUR KEYLINKS !!!!!!!

If you don't understand KeyLinks, just list all of the PK fields of your
underlying tables, taking them in order from left to right, plus any other
fields in the output that are required to achieve a distinct output row. I
don't know what your PK fields are but here's an example:

XMENUCATEGORY.XMENUCATEGORYID
XMENUCATEGORY.LOCATIONID
XMENUITEM.MENUITEMID
XMENUITEM.LOCATIONID
XMENUITEM.ITEMCODE

Another thing you could try (since we out here still have nothing but
guesses to work on regarding your metadata) is to test whether the
locationid ambiguity is being thrown up by your ordering criteria. Try
removing the ORDER BY clause from your statement (temporarily).

Another guess: are XMENUCATEGORY and XMENUITEM tables or are they views or
are they selectable stored procs? This is such a weird problem that there
just HAS to be something odd back there to account for IBO getting a
reference to your locationid field out of nowhere. It could ONLY have got
it from the metadata.

Do you have an obsolete TField sitting forgotten somewhere around your
query object? What happens if you do a global search for
YourQueryLocationID (substituting in the name of your query, of course...) ?

Helen