Helen,

Thank you very much for your response.... as usual... "right on"

Following is extracted. Full message is below.

At 8/13/01 01:49 PM +1000, you wrote:

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

OK, OK, I've been resisting..

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

You suggest that Params are inherently more efficient than Constants...
I would have thought "vice versa"

>You are almost always better of not using plans but tweaking the SQL instead.

Hmmmm, I thought so... with no "proof"

>You need to define a Mastersource for the detail dataset.

BINGO! Wow, It's easy to "overlook" some KEY (important) links.

Thank you VERY much for your help!

Phil Henningsen


At 8/13/01 01:49 PM +1000, you wrote:

>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
>_______________________________________________________
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/