At 09:48 PM 7/07/2004 +0000, you wrote:

>I have the following table:
>CREATE TABLE person (
> id INTEGER NOT NULL,
> name VARCHAR(50) NOT NULL,
> id_boss INTEGER NOT NULL,
> CONSTRAINT pk_person PRIMARY KEY (id)
>);
>ALTER TABLE person ADD CONSTRAINT fk_person FOREIGN KEY (id_boss)
> REFERENCES person(id);
>
>IB_Query.SQL =
>SELECT
> p1.*,
> p2.name Boss
>FROM
> person p1
> inner join person p2
> on p1.id_boss = p2.id
>
>IB_Query.FetchWholeRows = false
>IB_Query.KeyLinks = person.id
>IB_Query.KeyRelation = person
>
>when I open the query I get this error:
>
>
>Multiple rows in singleton fetch
>
>Check KeyLinks and JoinLinks properties
>
>SELECT
> p1.*,
> p2.name Boss
>FROM
> person p1
> inner join person p2
> on p1.id_boss = p2.id
>WHERE person.id=? /* BIND_0 */
>
>
>I understand why IB_Query and Firebird gets confused:
>"WHERE person.id=?"
>How to solve this? I've tried assigning to KeyLinks = p1.id but I get
>another error: "Invalid KeyLinks entry: p1.id."
>So ibo doesn't like me to use aliases in KeyLinks or KeyRelation.

Because it has no way to target a specific row in a specific table, as it
can with a regular join.

>What should I do?
>
>if you need a test-case I've uploaded one:
>http://groups.yahoo.com/group/IBObjects/files/Problem-KeyLinks-Alias.
>zip

You can't use KeyRelation to make a self-referencing join query
updatable. KeyRelation absolutely requires to be able to target a unique
row in the underlying KeyRelation member. Your flat structure doesn't
allow this.

There are various ways you could implement an updatable structure out of
this relationship.

1. If you really *need* the flat rows (e.g. for a tree control) create an
updatable view (using triggers) for the flat row. The server will require
you to specify distinct column names for each column. You will need to
include as many columns as it takes for IBO to be able to establish
KeyLinks when you request refreshes and updates on this set.
For example:

create view person_view (
ID,
NAME,
ID_BOSS,
BOSS,
BOSS_ID)
as
SELECT
p1.*,
p2.name Boss,
p2.id
FROM
person p1
inner join person p2
on p1.id_boss = p2.id

2. If you want the flat rows in the interface, without using a view, write
parameterised SPs as required for insert, update and delete and manually
pass the parameters to ib_dsql objects in the same transaction. Note that
you must include a unique key in the parameters. You should include p2.id
in the SELECT list of the read-only flat structure; also experiment with
including the rdb$db_key of each "hit" on the joining streams.

3. Unless you actually *need* the flat structure, implement the interface
as master-detail, using two datasets. Unless you are doing a tree
interface, this is the usual way to represent a self-referencing
relationship in a GUI. You can then use MasterLinks and/or
MasterParamLinks to implement the self-join.

4....there are other tricks, too, if you have a tight grip on the engine
internals. You might be interested to read Claudio Valderrama's papers on
rdb$db_key, at www.cvalde.net

Helen