Hi,
I recenty upgraded to the latest version of h2 (1.2.125)
Now the following does not work anymore ;
create cached table fintypes(
ID integer IDENTITY primary key ,
type char
);
create unique index fintype_idx on fintypes(type);
insert into fintypes values(null,'AAAA');
insert into fintypes values(null,'BBBB');
insert into fintypes values(null,'CCCC');
insert into fintypes values(null,'DDDD');
insert into fintypes values(null,'EEEE');
CREATE CACHED TABLE PUBLIC.FINACCOUNTS(
ID integer IDENTITY primary key ,
NR CHAR,
NAME CHAR,
STATUS CHAR,
TAG CHAR,
TYPE CHAR,
Foreign key(type) references fintypes(type)
);
create unique index finaccount_idx on FINACCOUNTS(nr);
create unique index finname_idx on FINACCOUNTS(name);
insert into finaccounts (ID, NR, NAME, STATUS, TAG, TYPE ) values
('31', '0001', 'AAA1AAA', 'OPEN', 'AA', 'AAAA');
insert into finaccounts (ID, NR, NAME, STATUS, TAG, TYPE ) values
('32', '0002', 'AAA2AAA', 'OPEN', 'AA', 'AAAA');
insert into finaccounts (ID, NR, NAME, STATUS, TAG, TYPE ) values
('33', '0003', 'AAA3AAA', 'OPEN', 'AA', 'AAAA');
insert into finaccounts (ID, NR, NAME, STATUS, TAG, TYPE ) values
('34', '0004', 'AAA4AAA', 'OPEN', 'AA', 'AAAA');
insert into finaccounts (ID, NR, NAME, STATUS, TAG, TYPE ) values
('30', '0005', 'BBB5BBB', 'OPEN', 'BB', 'BBBB');
insert into finaccounts (ID, NR, NAME, STATUS, TAG, TYPE ) values
('3', '0006', 'CCCC6', 'OPEN', 'EE', 'EEEE');
insert into finaccounts (ID, NR, NAME, STATUS, TAG, TYPE ) values
('1', '0007', 'BBBB7', 'OPEN', 'BB', 'BBBB');
insert into finaccounts (ID, NR, NAME, STATUS, TAG, TYPE ) values
('2', '0008', 'BBBB8', 'OPEN', 'BB', 'BBBB');
insert into finaccounts (ID, NR, NAME, STATUS, TAG, TYPE ) values
('4', '0009', 'CCCC999999999', 'AFGESLOTEN', 'CC', 'CCCC');
insert into finaccounts (ID, NR, NAME, STATUS, TAG, TYPE ) values
('5', '0010', 'EEEE0EEEE', 'OPEN', 'DD', 'DDDD');
insert into finaccounts (ID, NR, NAME, STATUS, TAG, TYPE ) values
('6', '0011', 'CCCC5CCCC', 'OPEN', 'EE', 'EEEE');
##
## Table : FINTRANS
##
CREATE CACHED TABLE PUBLIC.FINTRANS(
ID integer identity primary key,
DATUM timestamp,
ACCOUNTID CHAR,
AMOUNT FLOAT,
FOREIGN KEY(accountid) REFERENCES finaccounts(id)
);
create unique index findatum on fintrans(datum,accountid);
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('349',
'1993-10-01 00:00:00.0', '31', '2381.17');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('350',
'1993-12-01 00:00:00.0', '32', '2343.09');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('351',
'1993-12-01 00:00:00.0', '33', '268.25');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('352',
'1993-12-01 00:00:00.0', '34', '1597.65');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('353',
'1993-12-01 00:00:00.0', '30', '4416.12');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('354',
'1993-12-01 00:00:00.0', '3', '10777.94');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('355',
'1993-12-01 00:00:00.0', '1', '2981.84');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('356',
'1993-12-01 00:00:00.0', '2', '2381.17');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('357',
'1994-01-01 00:00:00.0', '4', '3304.97');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('358',
'1994-01-01 00:00:00.0', '5', '280.14');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('359',
'1994-01-01 00:00:00.0', '6', '1680.15');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('360',
'1994-01-01 00:00:00.0', '31', '4657.37');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('361',
'1994-01-01 00:00:00.0', '32', '11518.84');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('362',
'1994-01-01 00:00:00.0', '33', '3153.73');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('363',
'1994-01-01 00:00:00.0', '34', '2517.93');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('364',
'1994-03-01 00:00:00.0', '30', '2613.05');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('365',
'1994-03-01 00:00:00.0', '3', '280.14');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('366',
'1994-03-01 00:00:00.0', '1', '1680.15');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('367',
'1994-03-01 00:00:00.0', '2', '5902.22');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('368',
'1994-03-01 00:00:00.0', '4', '12113.79');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('369',
'1994-03-01 00:00:00.0', '5', '3475.99');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('370',
'1994-03-01 00:00:00.0', '6', '2840.19');
insert into fintrans (ID, DATUM, ACCOUNTID, AMOUNT ) values ('371',
'1994-05-01 00:00:00.0', '31', '3883.13');
create view PUBLIC.FINVIEW as select t.id,a.nr,a.name,a.status,
a.tag,a.type,t.datum,t.amount
from finaccounts a, fintrans t
where a.id = t.accountid
;
select datum"Datum",sum(amount)"Bedrag" from finview
where status='OPEN' and tag in ('AA','BB','CC','DD')
group by datum
order by datum;
the message is : No data found
This did work in 1.1.115,
kind regards
willy
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
