At 10:00 AM 06-12-00 +0100, you wrote:

>I am sorry I was not specific enough.
>
>This is the query:
>
>select
>T1.id
>,T1.id2
>,T2.description
>//This is needed for Ordering
>,(select description from t2 where t1.ID =t2.ID) as Description
>// That's what I meant with "select as statement" needed for TIB_LookUpCombo
>FROM t1 left outer join t2 on t1.id2=t2.id
>
>Only T1 will be updated and t2 serves as lookup table for t1.
>I would love to omit the "left outer join t2 on t1.id2=t2.id" and the field
>t2.description, but as I understand it, I need it for my Ordering Properties
>as I can not use a Calculated field (i.e. ,(select description from t2 where
>t1.ID =t2.ID) as Description) there.

OK. I think I can now see your confusion. If I am not mistaken, you are
trying to maintain the lookup dataset within the main (keysource) dataset
itself. This is not how the lookupcombo works.

First, for the main (keysource) dataset, you have chosen to use a subselect
so that you can display the description being linked-to, through the main
table's id2 column, to the primary key of the table providing the description.

So far, so good.

What I think you have missed is that you need ANOTHER dataset to provide
the optional selections for the lookupcombo. In IBO terms, this referred
to as the Lookup dataset.

We use the term "KeySource dataset" to refer to the main dataset, because
the lookup dataset links to it via its KeySource property. For that
property, you select the TIB_Datasource that links to the KeySource table.

OK, here is the SQL for the main (KeySource) dataset:

select T1.id ,
T1.id2 ,
(select description from t2 where t1.ID =t2.ID) as description
FROM t1
{{ plus a where clause containing SELECTION CRITERIA, not join
criteria }}

Let's call this qryT1 and name its datasource dsT1.
Make sure you go into the Fields Editor of qryT1 and check on the COMPUTED
attribute of the Description column.

Here is the SQL for the lookup dataset, let's call it qryT2:

select id, Description from T2

Add a TIB_Datasource for qryT2, let's call it dsT2.

Now, in the fields editor for qryT2, set its KeyLinks as
id=T1.id2

Set its KeySource property to dsT1.

Set its KeyDescLinks property to

T2.Description=Description

On the LookupCombo component, set its Datasource property to dsT2.

The linkages are now all done. When the main dataset first opens, each row
will have the Description column that was sub-selected from T2. From now
on, any updates to qryT1 will pick up the id value from the row selected in
the lookupcombo and write that id into the id2 column of the main
dataset. The row will be refreshed from the lookup dataset and, next time
the main dataset is refreshed, it will be re-populated from the sub-select.

If you want to embed the lookupcombo in the grid, just select it and cut it
Ctrl-X; then select the grid and drop the lookupcombo back into the grid
with Ctrl-V.

You don't need any joins and you won't need a KeyRelation.
Your main dataset will be "live" and the standard, automatic inserts,
updates and deletes will be created by IBO.

>Now what happens if I add the left
>outer join part, is, that the query is not updateable any more. However I
>thought defining T1 as KeyRelation would solve that problem.

It does solve the problem, for when you are updating a joined dataset, as
long as you have defined valid UpdateSQL statements for the KeyRelation
dataset. It is not applicable in this case.

>1. Do I have to use the part "left outer join t2 on t1.id2=t2.id" for the
>ordering properties or is there a way I can order on ,((select description
>from t2 where t1.ID =t2.ID) as Description )?

No.
It's still not clear exactly where you want to do this ordering. If you
want to fetch the main dataset in Description order, just add this:
select T1.id ,
T1.id2 ,
(select description from t2 where t1.ID =t2.ID) as description
FROM t1
order by 3

If you want the lookup dataset in Description order, add this:

select id, Description from T2
order by Description

>2. Why can't I edit the respective TIB_Grid when using the above query,
>shouldn't KeyRelation take care of this, as only 1 table will be updated?

I hope you understand now that use of KeyRelation still requires you to
define the UpdateSQL statements explicitly - it is not sufficient simply to
name the KeyRelation. But you don't need it for this dataset because it
isn't joined. "RequestLive" will make this dataset updateable by the
normal IBO rules.

Helen



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