At 07:06 AM 11/05/2007, you wrote:

>I'm using IBO4_5 and FireBird 1.5.
>I have a TIB_Grid based on a TIB_Query.
>With this query's SQL property it's allowed to edit fields on grid:
>SELECT
>O.DIO_CIIREG,O.DIO_DOCANO,O.DIO_DOCNUM,O.DIO_PARC,O.DIO_VENCTO,
>O.DIO_VALOR,O.DIO_BOOL,
>(SELECT D.DOC_LOTE FROM DOCUMENTO D WHERE D.DOC_CIIREG =
>O.DIO_CIIREG AND D.DOC_ANO = O.DIO_DOCANO AND D.DOC_NUM =
>O.DIO_DOCNUM) AS DOC_LOTE,
>(SELECT D.DOC_DOC FROM DOCUMENTO D WHERE D.DOC_CIIREG = O.DIO_CIIREG
>AND D.DOC_ANO = O.DIO_DOCANO AND D.DOC_NUM = O.DIO_DOCNUM) AS
>DOC_DOC,
>(SELECT I.PCI_DESCR FROM PARCERIA I WHERE I.PCI_REG = (SELECT
>D.DOC_PCIREG FROM DOCUMENTO D WHERE D.DOC_CIIREG = O.DIO_CIIREG AND
>D.DOC_ANO = O.DIO_DOCANO AND D.DOC_NUM = O.DIO_DOCNUM)) AS PCI_DESCR,
>(SELECT I.PCI_CCTCONTAF FROM PARCERIA I WHERE I.PCI_REG = (SELECT
>D.DOC_PCIREG FROM DOCUMENTO D WHERE D.DOC_CIIREG = O.DIO_CIIREG AND
>D.DOC_ANO = O.DIO_DOCANO AND D.DOC_NUM = O.DIO_DOCNUM)) AS
>PCI_CCTCONTAF,
>(SELECT P.PAR_RAZAO FROM PARCEIRO P WHERE P.PAR_REG = (SELECT
>I.PCI_PARREG FROM PARCERIA I WHERE I.PCI_REG = (SELECT D.DOC_PCIREG
>FROM DOCUMENTO D WHERE D.DOC_CIIREG = O.DIO_CIIREG AND D.DOC_ANO =
>O.DIO_DOCANO AND D.DOC_NUM = O.DIO_DOCNUM))) AS PAR_RAZAO
>FROM DIR_OBR O
>WHERE O.DIO_CIIREG= :ciireg
>ORDER BY O.DIO_BOOL DESC,O.DIO_VENCTO,O.DIO_PARC
>
>This other equivalent SQL (using JOIN clause) gives same result set,

It is not equivalent SQL and the result set is not the same. In the
first set, you will get one record for each row in DIR_OBR that meets
the WHERE criterion, including NULL in positions where any subquery
returns no result. Only the natural columns (from DIR_OBR) will be updatable.

>but it's NOT allowed to edit fields:
>SELECT
>O.DIO_CIIREG,O.DIO_DOCANO,O.DIO_DOCNUM,O.DIO_PARC,O.DIO_VENCTO,
>O.DIO_VALOR,O.DIO_BOOL,
>D.DOC_LOTE,D.DOC_DOC,D.DOC_PCIREG,I.PCI_DESCR,I.PCI_CCTCONTAF,P.PAR_R
>AZAO
>FROM ((DIR_OBR O INNER JOIN DOCUMENTO D ON O.DIO_CIIREG =
>D.DOC_CIIREG AND O.DIO_DOCANO = D.DOC_ANO AND
>O.DIO_DOCNUM=D.DOC_NUM) INNER JOIN PARCERIA I ON D.DOC_PCIREG =
>I.PCI_REG) IINER JOIN PARCEIRO P ON I.PCI_PARREG = P.PAR_REG
>WHERE O.DIO_CIIREG= :ciireg
>ORDER BY O.DIO_BOOL DESC,O.DIO_VENCTO,O.DIO_PARC

This set (which contains incorrect syntax) does not contain any
updatable output fields, because of the joins. It will produce one
row for each row in DIR-OBR that meets the WHERE criterion and *also*
meets all of the JOIN criteria.

>Both querys KeyLinks is set to the primary key of table O (Dir_Obr)
>and RequestLive to True

That KeyLinks setting will work for the first query but not for the
second one. Your Keylinks for the second one will be much more
complex: you need to provide enough keys to describe an output set
in which every row is unique.

Because of the joins, RequestLive does not work for the second
set. This is a limitation of SQL, which can operate DML on only one
table per statement. In IBO you can make a joined set behave like an
updatable set by providing statements for the XxxxSQL properties -
usually these would be EXECUTE PROCEDURE statements that pass the
KeyLinks to stored procedures as input parameters.

If you want to update only Dir_Obr from the second set, you can set
the KeyRelation property to Dir_Obr and then RequestLive will make
the columns from Dir_Obr updatable. (This is an IBO feature: it
does not change the capabability of the SQL database engine.)

> So I suppose there is some kind of limitation when using JOIN clause.

Yes.

>Anyway, I'm confused because with this another query using JOIN edit
>IS allowed:
>SELECT G.meg_ciireg, G.meg_metodo, G.meg_ug, G.meg_grcod,
>G.meg_trivial,
>G.meg_inc, G.meg_alt, G.meg_exc, M.met_descr as metdescr, M.met_tipo
>as mettipo
>FROM Metodo_GRP G INNER JOIN Metodo M
>ON G.meg_ciireg = M.met_ciireg AND G.meg_metodo = M.met_metodo
>WHERE (G.meg_ciireg = :ciireg) AND (G.meg_metodo >= 0)

Natural fields from one of the contributing tables will be "editable"
in IBO if you have applied the KeyRelation to that table. Derived
fields will still be non-updatable. Or that set could behave like an
updatable set if you provided valid XxxxSQL for updates, inserts and deletes.

>Well, what is the trick? Is there any software (IBO or FireBird?)
>limitations about this subject? What about later software versions?

As I mentioned previously, there is no "trick" to change the rules of
SQL. IBO has the described tricks to make a non-updatable set behave
as though it is updatable.

However, it won't work if the Keylinks are missing, wrong or
incomplete, and your SQL has to be completely correct in order for
the IBO parser to make proper sense out of it.

Here is the corrected syntax for your second statement:

SELECT
O.DIO_CIIREG,
O.DIO_DOCANO,
O.DIO_DOCNUM,
O.DIO_PARC,
O.DIO_VENCTO,
O.DIO_VALOR,
O.DIO_BOOL,
D.DOC_LOTE,
D.DOC_DOC,
D.DOC_PCIREG,
I.PCI_DESCR,
I.PCI_CCTCONTAF,
P.PAR_R

FROM DIR_OBR O
INNER JOIN DOCUMENTO D
ON O.DIO_CIIREG = D.DOC_CIIREG
AND O.DIO_DOCANO = D.DOC_ANO
AND O.DIO_DOCNUM=D.DOC_NUM
INNER JOIN PARCERIA I
ON D.DOC_PCIREG = I.PCI_REG
InNER JOIN PARCEIRO P ON I.PCI_PARREG = P.PAR_REG
WHERE O.DIO_CIIREG= :ciireg
ORDER BY O.DIO_BOOL DESC,O.DIO_VENCTO,O.DIO_PARC

Those extra brackets you included in your statement are not legal
syntax, although the Firebird engine will try to ignore them if the
statement is presented to the engine unmodified. The problem that
you create by using them is that you cause the IBO parser to get
confused about which phrases of your SELECT statement apply to each
structure that IBO creates internally when it tries to make your
non-updatable statement behave as though it were "live".

For this set, the KeyLinks must include AT LEAST the primary key of
DIR_OBR and each of the JOIN keys. It is possible that you would
need to include more, if those keys are insufficient to define a unique row.

Helen