At 09:35 PM 8/05/2005 +0000, you wrote:

>Hi,
>
>I have a TIB_Query and an IB_Grid. (IBO 4.2I)
>SQL.Text:
>SELECT
> ITEMS.ID AS ITEM_ID,
> ITEMS.NAME AS ITEM_NAME,
>FROM
> ITEMS
> left outer join item_hierarchy_relations IH on (ITEMS.ID =
>IH.CHILD_ITEM_ID and IH.ITEM_HIERARCHY_ID = :IHID)
>
>(Left outer join with parameter)
>
>OrderingItems:
>Name=ITEMS.NAME;ITEMS.NAME DESC
>
>OrderingLinks:
>ITEM_NAME=ITEM=1
>
>If I click to header of grid the grid will be empty.
>When I debug this I suprised:
>The order changing clear the parameters of query.
>
>My question is why and how can i solve this problem?

Under the hood, changing the order *does* clear the params temporarily,
since it involves changing (or adding) an ORDER BY clause.

Your problem comes from several sources.

1. This statement will return an empty set whenever the value of :HID is
not found in item_hierarchy_relations.

2. You have a curious mixture of table identifiers and aliases in this
statement. This can causes ambiguity problems for the server engine.

3. Because you have mixed the join criteria with the search criteria in
your JOIN...ON clause, IBO has no choice but to completely invalidate the
original statement when the value of :HID changes. Is there any special
reason why you cannot move the search criterion to a WHERE clause?

4. However, the overwhelming cause of your problem is your attempt to order
by a derived field. ITEM_NAME is not a database column. The KeyLinks
column is also not a database column, which will potentially cause
difficulties with searches.

SELECT
ITEMS.ID AS /* ITEM_ID */,
ITEMS.NAME /* AS ITEM_NAME */

If you want your grid columns to display "user-friendly" names, use the
DisplayLabel property.

Also, the left join potentially delivers completely null records, where
both the KeyLinks and the OrderingLinks are null. Since you are not
fetching any columns from the right-side table, why are you using an outer
join?

May I suggest one of the following for your SQL statement?

1 - using table identifiers consistently

SELECT
ITEMS.ID,
ITEMS.NAME
FROM
ITEMS
join item_hierarchy_relations
on ITEMS.ID = item_hierarchy_relations.CHILD_ITEM_ID
WHERE item_hierarchy_relations.ITEM_HIERARCHY_ID = :IHID

2 - using table aliases consistently

SELECT
I.ID,
I..NAME,
FROM
ITEMS i
join item_hierarchy_relations IH
on I.ID = IH.CHILD_ITEM_ID
WHERE IH.ITEM_HIERARCHY_ID = :IHID

Helen