In general, joined tables aren't updatable. However, IBO provides a few
options to make them updatable. I think you can use KeyLinks (or
something similar) to make one of the tables updatable or alternatively
you can specify an UpdateSQL statement. If you want to update both
tables, I think your only option is to write a stored procedure that
does the update and write a statement in the UpdateSQL property to tell
IBO how to update.

By the way, maybe you can get away even simpler. Try something like:

SELECT O.DIO_DOCNUM,O.DIO_VALOR,
(SELECT D.DOC_LOTE FROM DOCUMENTO D WHERE D.DOC_NUM = O.DIO_DOCNUM)
AS DOC_LOTE
FROM DIR_OBR O
WHERE O.DIO_DOCNUM > 0 AND
EXISTS(SELECT * from DOCUMENTO D2
WHERE D2.DOC_NUM = O.DIO_DOCNUM
AND D2.DOC_LOTE IS NOT NULL)

The error you got was simply because DOC_LOTE is only part of your
subselect and as such not available in your WHERE clause (this field
won't be updatable in the above query).

Set

Roberto Freitas wrote:

> Hello,
> I am using Firebird-1.5.2.4731, IBO4_5_B and Delphi 7.
> I need to obtain an updatable recordset from 2 tables, I've tried 2
> options, but I couldn't get it.
>
> 1) First option: I get this error message = Column unknowm DOC_LOTE
> SELECT O.DIO_DOCNUM,O.DIO_VALOR,
> (SELECT D.DOC_LOTE FROM DOCUMENTO D WHERE D.DOC_NUM = O.DIO_DOCNUM)
> AS DOC_LOTE
> FROM DIR_OBR O
> WHERE O.DIO_DOCNUM > 0 AND DOC_LOTE IS NOT NULL
>
> 2) Second option: returns rows correctly, I show it on a TIB_Grid,
> but it is not updatable (I don't known if it's not updatable due to
> FireBird or IBO)
> SELECT O.DIO_DOCNUM,O.DIO_VALOR,D.DOC_LOTE
> FROM DIR_OBR O INNER JOIN DOCUMENTO D ON D.DOC_NUM = O.DIO_DOCNUM
> WHERE O.DIO_DOCNUM > 0 AND D.DOC_LOTE IS NOT NULL
>
> How can I do it? Thanks a lot.