Adodataset Delphi Edit

Question

I have a ADODataSet with the following consultation

DECLARE @ID_CONTRATO_EMPRESARIAL INTEGER = :ID_CONTRATO_EMPRESARIAL
BEGIN
WITH V1 AS (
select C.ID_CONTRATO, 
    COUNT(CASE AD.FUNCIONARIO WHEN 'TRUE' THEN 1 ELSE NULL END) FUNCIONARIOS,
    COUNT(*) MEMBROS
FROM CONTRATO C, ADESAO AD
WHERE C.ID_CONTRATO_EMPRESARIAL = @ID_CONTRATO_EMPRESARIAL  
    AND C.ID_CONTRATO = AD.ID_CONTRATO
GROUP BY C.ID_CONTRATO, C.NUMERO, C.VALOR_CLIENTE)
SELECT 
    V1.FUNCIONARIOS, V1.MEMBROS,
    PT.ID_PESSOA AS ID_PESSOA_TITULAR, PT.NOME AS NOME_TITULAR,
    C.*
FROM V1, CONTRATO C, PESSOA PT
WHERE V1.ID_CONTRATO = C.ID_CONTRATO
    AND C.ID_PESSOA = PT.ID_PESSOA
END

I wonder if I have a way Edit in that DataSet and save the changed column in the bank.

Basically what I’m trying to do is this

    dmContratoEmpresarial.cdsContratoEmpresarialContratos.Edit;
    dmContratoEmpresarial.cdsContratoEmpresarialContratosVALOR_TI.Value := 1000;
    dmFramework.Post(dmContratoEmpresarial.cdsContratoEmpresarialContratos);

Where the column VALOR_TI is on the table CONTRATO. However when checking the seat the column has not been changed.

Answer 1

Standard approach

The general practice is: when one wants to edit the result of a query involving multiple tables and update the database, one recovers in a separate query the records only of the table to edit.

That is, the simplest would be to get in this complex query the id of the record you need to edit, and then get this record to edit in another simple query and edit it in another dataset, or even make an SQL update command directly on the record.

An option

On the other hand, a ADO query offers the property Unique Table, in which you can specify the table on which operations of Insert, delete, and update will be applied when the query result was obtained from more than one table. More or less like this:

ADOQuery.Properties['Unique Table'].Value := 'CONTRATO';

However, since your query is quite complex, it is possible that ADO cannot solve it even with this property set; hence the default approach.