Hello IB support.

This "SELECT" returns 2 rows

select CustomerID, Total from vByCustomer where CustomerID = 38500
-------------------------------------------------------
38500 252.0000
38500 -504.0000

"SELECT" part of the view "vByCustomer" contains 3 records for 38500:
-------------------------------------------------------
38500 252.0000
38500 -504.0000
38500 252.0000

So final view "vCustSummary" returns wrong data.
I guess "vByCustomer" removes equal rows.
That is why I've added column OPID to the "vByCustomer",
that defined as CAST(MAX(x.ID) AS INTEGER) as OPID for each part of union.

After that server crashes every time when accessing that modified view.
I've worked it around by replacing MAX(OPID) to constant 1..4 respectively.

Why views? Because it works and performance is acceptable:
"select * from vCustSummary" is executed about 2-3s.
And the best result of single-pass "SELECT" is about 15 sec.
I do not know why.

These are my tables :

/* DDL */
create table optypes (
id smallint not null primary key,
/* .... */
Factor smallint not null
);

create table paytypes (
id smallint not null primary key,
/* .... */
Factor smallint not null
);

create table subjects (
id integer not null primary key,
/* .... */
name varchar(100) not null
);

create table ops (
id integer not null primary key,
typeid smallint not null references optypes(ID),
SrcID integer not null references subjects (ID),
DstID integer not null references subjects (ID),
/* .... */
Price float not null,
Qty integer not null,
);

create table pays (
id integer not null primary key,
typeid smallint not null references paytypes(ID),
SrcID integer not null references Subjects (ID),
DstID integer not null references Subjects (ID),
/* .... */
Total float not null,
Rate float not null,
check (Rate > 0)
);

create view vByCustomer (CustomerID, Factor, Total) as
select x.srcid as CustomerID, t.factor,
cast(sum(x.Price*x.Qty) as float) as Total
from ops x
inner join optypes t on x.typeid = t.id and t.factor = 1
group by x.srcid, t.factor
union
select x.dstid as CustomerID, cast(0 as smallint) as factor,
cast(-sum(x.Price*x.Qty) as float) as Total
from ops x
inner join optypes t on x.typeid = t.id and t.factor = -1
group by x.dstid, t.factor
union
select x.srcid as CustomerID, t.factor,
cast(sum(x.Total/x.Rate) as float) as Total
from pays x
inner join paytypes t on x.typeid = t.id and t.factor = 1
group by x.srcid, t.factor
union
select x.dstid as CustomerID, cast(0 as smallint) as factor,
cast(-sum(x.total/x.rate) as float) as Total
from pays x
inner join paytypes t on x.typeid = t.id and t.factor = 1
group by x.dstid, t.factor;

CREATE VIEW vCustSummary(id, Balance, TotalIn) AS
select x.CustomerID,
cast(Sum(x.total) as float) as Balance,
cast(Sum(x.total*x.factor) as float) as TotalIn
from vSubjTotal x
group by x.CustomerID;

/* end of DDL */

BTW Firebird SS 1.0.796 32-bit IO under RedHat Linux 7.2.

Thank you.

Andrew.