Subject | query update with JOIN in SQL clause |
---|---|
Author | Roberto Freitas |
Post date | 2007-05-10T21:06:09Z |
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,
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
Both querys KeyLinks is set to the primary key of table O (Dir_Obr)
and RequestLive to True
So I suppose there is some kind of limitation when using JOIN clause.
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)
Well, what is the trick? Is there any software (IBO or FireBird?)
limitations about this subject? What about later software versions?
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,
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
Both querys KeyLinks is set to the primary key of table O (Dir_Obr)
and RequestLive to True
So I suppose there is some kind of limitation when using JOIN clause.
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)
Well, what is the trick? Is there any software (IBO or FireBird?)
limitations about this subject? What about later software versions?