At 07:01 PM 12-08-01 -0700, you wrote:

>Subj: Master-Detail grids not linking
>
>I think (newbie, that I am) that the problem is related to the "union" in
>the Detail query...
>
>IB_Grid_Mas (the Master) is ok.
>It's IB_Query_Mas.SQL.Text is: (the DB has several Million records
>spanning 2 months)

First, just a recommendation that you change the double quotes around the concatenated blank to single quotes...future protection...if you were already using ib6 dialect 3 this statement would throw an error.

>select O.Order_Number, O.FirstName||" "||O.LastName AS Customer_Name,
> O.Email, O.Record_Timestamp, O.Online_Status,
> O.Pending_Count AS Pend, O.Amount,
> O.Session_Aff, O.Credited_Aff,
> O.Parameters as OrdParams
> from S_ORDERS O
> where (O.RECORD_TIMESTAMP >= "08/12/2001") /* use param >= :STARTTIME */
> and (O.RECORD_TIMESTAMP < "08/13/2001") /* use param < :ENDTIME */
> plan (O INDEX (S_ORDERS_BY_TIMESTAMP))

Similar here - double quotes are no longer interchangeable with single quotes, i.e. your date literals will need to be single-quoted.

Is this index on RECORD_TIMESTAMP? If so, try out this query in IB_SQL with a monitor, to see if your plan is killing the optimizer. You are almost always better of not using plans but tweaking the SQL instead.

>(Actually, the "where" clause is generated at run-time from several user
>inputs.)

You should change your SQL property to either (a) use parameters in the WHERE clause or (b) exclude the WHERE clause and use SQLWhereItems in the datasets' OnPrepareSQL methods.

>IB_Query_Mas.KeyLinks = S_ORDERS.ORDER_NUMBER

OK.

>IB_Grid_Det (the Detail) is NOT OK (ie empty).
>I think that the problem is related to the "union"

In a kind of a way, it is. But more problems exist because you aren't using parameters and I think you have misunderstood KeyLinks and MasterLinks somewhat.

You need to define a Mastersource for the detail dataset.

>It's IB_Query_Det.SQL.Text is:
>
>select Order_Number, 'B' as Type, Bundle_Number,
> cast('' as VarChar(3)) as Item,
> Record_Timestamp, Pending_Count as Pend,
> cast(Product_Id as integer) as Product,
> "" as Description, Parameters
> from S_BUNDLES where Order_Number = :Order_Number
>union all
>select Order_Number, 'I' as Type, Bundle_Number,
> cast(Item_Number as VarChar(3)) as Item,
> Record_Timestamp, Pending as Pend,
> Product_Id as Product,
> "" as Description, Parameters
> from S_ITEMS where Order_Number = :Order_Number
>
>Currently, IB_Query_Det.KeyLinks =
> ORDER_NUMBER
> BUNDLE_NUMBER
> ITEM
>but other variations made no difference.

The rule for the KeyLinks is that it should be all columns required to identify a row uniquely. When this is a "simple select", it will be the primary key of the dataset. Will you get a unique row simply with ORDER_NUMBER and BUNDLE_NUMBER? ITEM is not a candidate for a KeyLink because it is a computed column, and so,in succeeding queries, cannot be referenced to a column in a table.

>Should it contain TYPE? (a "virtual field")

No, that is not a candidate either, for the same reason. Also - more future protection - give this alias a different name, e.g XTYPE, because "TYPE" is a now a reserved keyword.

>IB_Query_Det.MasterLinks is empty, although I have tried:
> S_BUNDLES.ORDER_NUMBER=S_ORDERS.ORDER_NUMBER
> S_ITEMS.ORDER_NUMBER=S_ORDERS.ORDER_NUMBER

No, this won't work because the links won't resolve for every candidate row. The dataset has no way to know which values came from which table.

Try this instead:

ORDER_NUMBER=S_ORDERS.ORDER_NUMBER

>Currently, IB_Query_Det.MasterParamLinks =
> ORDER_NUMBER=S_ORDERS.ORDER_NUMBER

I'd expect this to work with correct MasterSource and MasterLinks. Set up an IB_MonitorDialog set up to look at the statements being passed...

>The detail grid is empty.
>
>(ps: the queries do work. they are unchanged from the previous
>version that worked fine, but slowly... using IB Express and
>InfoPower.)

Think about dropping the plan; also check that you don't have any duplicate indexes on these tables. This occurs when you define your own index for a column that is also a primary or foreign key. IB doesn't stop you doing this but it results in NO index being used by the optimizer.

hth
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________