At 12:07 AM 6/06/2003 +0000, you wrote:

>If I have a Query with the following
>SELECT T1.ID, T2.ID
>FROM TABLE1 T1
>LEFT OUTER JOIN TABLE1 T2
>ON (T1.ID = T2.PARENTID)
>
>The proper KeyLinks value is T1.ID not TABLE1.ID. However the system
>gives me an error that this is an invalid keylinks.
>
>What am I doing wrong? I can' imagine that noone out there is doing
>tree type joins and using aliases.

Since this is not a naturally-updatable table anyway, it should be possible
to use the RDB$DB_KEY of the set as the Keylinks (the column name is
DB_KEY: every output set has it). Don't specify a table identifier at
all, since the DB_KEY of a joined set is a composite (constructed by the
server) of the primary keys of the underlying logical tables. (The only
"gotcha" that I can't predict is how the nulls from the outer join will
affect the DB_KEY...you would need to experiment with that...)

You can't use the DB_KEY as the "automatic positioner" for a searched
update but it should be able to satisfy the unique-row-identifier
requirement for KeyLinks, e.g. to locate/relocate the buffered row in the
current transaction context. DB_KEY is supposed to be unique - there's
just that little niggle in my head that *maybe* t1.id + a null t2.id
(derived via the outer join) generates identical db_keys...

With a re-entrant join, you'd normally achieve updatability for joined sets
in your code, by reading unique input values from the output set and
passing them to stored procedure parameters in your XXXXSQL properties.

However, this statement, per se, doesn't include enough non-null columns to
establish the uniqueness of each node relationship, anyway (parentID isn't
in the output set and the outer join means you have nulls in the T2 ID) so
it can't be made "live" under any conditions. I assume updatability is not
an issue for you in the current case.

It's worth understanding that a DB_KEY keyvalue won't survive a hard
commit, so bookmarking it for relocation on refresh after a hard commit
won't be reliable.

Helen