delphi159

Update2:
Strange case: If I create clientdataset using fielddefs then it works without error and applies changes in DB but when I assign local dataset then after 3-4 close-opening of program delta-packet becomes unassigned(nil).  
Instead  of firebird  db I created MSSQL db but  nothing changed, the same error.
After that I make these changes:
fdQuery's fetch options: mode  fmOnDemand change with fmAll,  RowSetSize 50 with 500,
clientdataset's option  fetchOnDemand true with false
DataSetProvider's option AllowMultiSelectUpdate false with true but nothing has changed. 

Sql for firebird dataset is very simple:

CREATE TABLE GOODS (
    GOODS_ID  INTEGER NOT NULL,
    GOOD      VARCHAR(50),
    QNT       VARCHAR(1) DEFAULT '0' NOT NULL
); 

Sql for MS Sql server: 
 

CREATE TABLE [dbo].[Goods](
    [Goods_id] [int] IDENTITY(1,1) NOT NULL,
    [Good] [varchar](50) NOT NULL,
    [Qnt] [smallint] NOT NULL,
 CONSTRAINT [PK_Goods] PRIMARY KEY CLUSTERED 
(
    [Goods_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Brian Evans, thanks for you reply.  Unfortunately, you are right. 

Despite many attempts, it still doesn't work. It's very sad. What may be the reason if in the case of a remote server (internet hosting) in datasnap project it works perfectly and sends data packets containing thousands of records? 

I have already changed it with fdQuery+fdTableAdapter+fdCommand and it works fine. 

Update: 
 After closing-opening program 2-3 times, for no clear reason when I open program delta becomes unassigned (nil) as breakpoint shows. Therefore, after that calling of applyupdates() or mentioning of clientdataset.delta causes exception "access violation". 

I have "briefcase model application". Clientdataset reads data from xml-file. After closing modified data are saved in the xml-file. When click on "applyUpdate" button, data are saved in firebird db.

If I don't exit program then ApplyUpdate works properly and commits delta-packet in db but sometimes, after closing-opening program 3- 4-5 times, for no clear reason delta becomes empty and after that if I click on "apply" button ApplyUpdates(-1)  operator causes exception "access violation".

Delphi 10.4, update 2. Firebird3.

Project and db files on google drive:  https://drive.google.com/drive/folders/1UvtGmaWLdmmSYQITWpEvp-BwRNmw9VEL?usp=sharing

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, FireDAC.Stan.Intf,
  FireDAC.Stan.Option, FireDAC.Stan.Param, FireDAC.Stan.Error, FireDAC.DatS,
  FireDAC.Phys.Intf, FireDAC.DApt.Intf, FireDAC.Stan.Async, FireDAC.DApt,
  Datasnap.DBClient, FireDAC.Comp.DataSet, FireDAC.Comp.Client,
  Datasnap.Provider, Vcl.StdCtrls, Vcl.Buttons, Vcl.ComCtrls, Vcl.Grids,
  Vcl.DBGrids, FireDAC.UI.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool,
  FireDAC.Phys, FireDAC.Phys.FB, FireDAC.Phys.FBDef, FireDAC.VCLUI.Wait, MidasLib;

type
  TForm1 = class(TForm)
    LabelStatus: TLabel;
    LabelChangeCount: TLabel;
    DBGrid: TDBGrid;
    bApply: TBitBtn;
    DBGrid2: TDBGrid;
    bClose: TButton;
    bOpen: TButton;
    bMerge: TBitBtn;
    DataSource1: TDataSource;
    DataSetProvider1: TDataSetProvider;
    qGoods: TFDQuery;
    cdsGoods: TClientDataSet;
    DataSource2: TDataSource;
    FDConnection1: TFDConnection;
    FDTransaction1: TFDTransaction;
    qGoodsGOODS_ID: TIntegerField;
    qGoodsGOOD: TWideStringField;
    qGoodsQNT: TWideStringField;
    procedure bApplyClick(Sender: TObject);
    procedure bCloseClick(Sender: TObject);
    procedure bOpenClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure cdsGoodsAfterPost(DataSet: TDataSet);
    procedure DataSource1DataChange(Sender: TObject; Field: TField);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;
  FN: TFileName;
  MySavePoint: integer;
  Modified: boolean;


implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
 qGoods.Open;

 FN:= ExtractFilePath(ParamStr(0)) + 'cds.xml';
 cdsGoods.filename:= FN;
 cdsGoods.Close;
 cdsGoods.open;
 MySavePoint:= cdsGoods.SavePoint;
end;


procedure TForm1.bApplyClick(Sender: TObject);
begin
 cdsGoods.ApplyUpdates(-1);
 qGoods.Refresh;
 MySavePoint:=cdsGoods.SavePoint;
 Modified:= false;
end;


procedure TForm1.bCloseClick(Sender: TObject);
begin
 cdsGoods.Close;
end;

procedure TForm1.bOpenClick(Sender: TObject);
begin
 cdsGoods.Open;
end;

procedure TForm1.cdsGoodsAfterPost(DataSet: TDataSet);
begin
 Modified:= true;
end;

procedure TForm1.DataSource1DataChange(Sender: TObject; Field: TField);
begin
 case cdsGoods.UpdateStatus of
     usUnmodified: labelStatus.Caption:='record didnt change';
     usModified:   labelStatus.Caption:='record was modified';
     usInserted:   labelStatus.Caption:='record was inserted';
     usDeleted:    labelStatus.Caption:='record was deleted';
    end;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
 qGoods.close;
 cdsGoods.Close;
end;

procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
 if Modified then  case application.MessageBox(PwideChar('Data were changed. Save changes?'), '', MB_YESNOCANCEL+MB_ICONQUESTION) of
                     IDCancel: CanClose:=false;
                     IDNo:  cdsGoods.SavePoint:=MySavePoint;
                     // IDYES- no needed. Cds is saved auomatically after close
// cdsGoods.saveToFile(FN); 
                   end;

end;

   
end.

Just now, Dany Marmur said:

Just a quick guess, (i'm not an IB user); try sum(coalesce(dt1.field, 0)) it should produce the same result.

If IB has CTEs then i guess it would be a bit easier to map fields. Also just a guess.

Dany Marmur, unfortunatelley a new error appeared: 'Expression Evalution Not Supported' on IB 2020. 

My final aim is this sql-code:

select  
      G.Goods,  
      G.Goods_id,
      coalesce(dt2.incomes,0)as incomes,
      coalesce(dt2.sales,0) as sales,
      coalesce(dt2.writeoffs,0) as writeoffs,
      coalesce(dt2.endqnt,0) as endqnts
From Goods G
Join
(
select                                                                          
      Goods_id,
      coalesce(sum(inc),0) as incomes,
      coalesce(sum(sale),0) as sales,
      coalesce(sum(writeoff),0) as writeoffs,
      sum(coalesce(inc,0)-coalesce(sale,0)-coalesce(writeoff,0)) as endqnts
    from(
        select 
            i.goods_id, 
            sum(i.qty) as inc,
            cast(0 as float) as sale,
            cast(0 as float) as writeoff
        from income i
        where cast(i.recdate as date) <= :d  
        group by i.goods_id  
     union all
        select 
            s.goods_id, 
            cast(0 as float),
            sum(s.qty) as sale,
            cast(0 as float)
         from sales s
         where cast(s.recdate as date) <= :d 
         group by s.goods_id    
     union all
        select
            w.goods_id,
            cast(0 as float), 
            cast(0 as float),
            sum(w.Qty)  as writeoff, 
        from writeoff w
        where cast(i.recdate as date) <= :d   
        group by w.goods_id) dt1 
   group by Goods_id) dt2
   on P.Goods_id=dt2.Goods_id
   order by Goods

but this dt2+dt1 derived tables code 

select                                                                          
      Goods_id,
      coalesce(sum(inc),0) as incomes,
      coalesce(sum(sale),0) as sales,
      coalesce(sum(writeoff),0) as writeoffs,
      sum(coalesce(inc,0)-coalesce(sale,0)-coalesce(writeoff,0)) as endqnts
    from(
        select 
            i.goods_id, 
            sum(i.qty) as inc,
            cast(0 as float) as sale,
            cast(0 as float) as writeoff
        from income i
        where cast(i.recdate as date) <= :d  
        group by i.goods_id  
     union all
        select 
            s.goods_id, 
            cast(0 as float),
            sum(s.qty) as sale,
            cast(0 as float)
         from sales s
         where cast(s.recdate as date) <= :d 
         group by s.goods_id    
     union all
        select
            w.goods_id,
            cast(0 as float), 
            cast(0 as float),
            sum(w.Qty)  as writeoff, 
        from writeoff w
        where cast(i.recdate as date) <= :d   
        group by w.goods_id) dt1 
   group by Goods_id

returns this error on Interbase 2020 server:

Error at line 1. Dynamic SQL Error: Error at line 1. sql error code=-206. Column unknown INC.
I tried a lot of attempts write code with liases but without success.

 How to correct this code for Interbase 2020 server?