jdbc:h2:/home/jjdelcerro/Descargas/vcstest;MODE=PostgreSQL;SCHEMA=PUBLIC;ALLOW_LITERALS=ALL

create table vcsgis_DATA (
  "cod_branch" varchar(40),
  "data_revnumber" int,
  "data_operation" int,
  "data_identifier" varchar(40),
  "data_values" varchar
);

insert into vcsgis_DATA ("cod_branch", "data_revnumber", "data_operation", "data_identifier", "data_values") VALUES ( 'master', 1, 2 /*INS*/, '1', 'AAA');
insert into vcsgis_DATA ("cod_branch", "data_revnumber", "data_operation", "data_identifier", "data_values") VALUES ( 'master', 1, 2 /*INS*/, '2', 'BBB');
insert into vcsgis_DATA ("cod_branch", "data_revnumber", "data_operation", "data_identifier", "data_values") VALUES ( 'master', 1, 2 /*INS*/, '3', 'CCC');
insert into vcsgis_DATA ("cod_branch", "data_revnumber", "data_operation", "data_identifier", "data_values") VALUES ( 'master', 2, 1 /*UPD*/, '2', 'BBB2');
insert into vcsgis_DATA ("cod_branch", "data_revnumber", "data_operation", "data_identifier", "data_values") VALUES ( 'master', 2, 2 /*INS*/, '4', 'DDD');


cod_branch    data_revnumber    data_operation    data_identifier   data_values  
master        1                 2                 1                 AAA
master        2                 1                 2                 BBB2
master        1                 2                 3                 CCC
master        2                 2                 4                 DDD


insert into vcsgis_DATA ("cod_branch", "data_revnumber", "data_operation", "data_identifier", "data_values") VALUES ( 'master', 2, 2 /*INS*/, '5', 'EEE');
insert into vcsgis_DATA ("cod_branch", "data_revnumber", "data_operation", "data_identifier", "data_values") VALUES ( 'master', 2, 0 /*DEL*/, '3', NULL);
insert into vcsgis_DATA ("cod_branch", "data_revnumber", "data_operation", "data_identifier", "data_values") VALUES ( 'master', 3, 2 /*INS*/, '6', 'FFF');
insert into vcsgis_DATA ("cod_branch", "data_revnumber", "data_operation", "data_identifier", "data_values") VALUES ( 'master', 3, 0 /*DEL*/, '5', NULL);
insert into vcsgis_DATA ("cod_branch", "data_revnumber", "data_operation", "data_identifier", "data_values") VALUES ( 'master', 4, 1 /*UPD*/, '1', 'AAA2');


cod_branch    data_revnumber  	data_operation  	data_identifier  	data_values  
master        4                 1                 1                 AAA2
master        2                 1                 2                 BBB2
master        2                 2                 4                 DDD
master        3                 2                 6                 FFF



SELECT * FROM vcsgis_DATA ORDER BY "data_identifier","data_operation";


-- CHECKOUT
SELECT vcsgis_DATA.* FROM 
  ( SELECT "data_identifier", MAX("data_revnumber") AS "data_revnumber" FROM 
      vcsgis_DATA 
    WHERE "cod_branch" = 'master'
    GROUP BY "data_identifier" 
    HAVING MIN("data_operation")<>0
  ) AS T1
LEFT JOIN 
  vcsgis_DATA
ON T1."data_identifier" = vcsgis_DATA."data_identifier" AND 
   T1."data_revnumber" = vcsgis_DATA."data_revnumber"
;


-- INSERTEDS AND UPDATEDS
SELECT vcsgis_DATA.* FROM 
  ( SELECT "data_identifier", MAX("data_revnumber") AS "max_revnumber" FROM 
      vcsgis_DATA 
    WHERE "cod_branch" = 'master'AND "data_revnumber" >=1
    GROUP BY "data_identifier" 
    HAVING MIN("data_operation")<>0
  ) AS T1
LEFT JOIN 
  vcsgis_DATA
ON T1."data_identifier" = vcsgis_DATA."data_identifier" AND 
   T1."max_revnumber" = vcsgis_DATA."data_revnumber"
;

