Application: gvSIG desktop

            features0 = null;

            String[] header = new String[]{"ID", "Comunidad", "Provincia", "Ciudad", "Poblacion", "Densidad", "Fecha", "CompuID", "CompuPob", "CompuProv", "ExtraID900", "ExtraPobDen", "ExtraAno"};

            ArrayList<Object[]> values = new ArrayList<>();

            values.add(new Object[]{5, "GVA", null, null, 200, null, null, 5, 201, "ALICANTE", 905, 206, 2019});

            values.add(new Object[]{6, "GVA", null, null, 20, null, null, 6, 422, "ALICANTE", 1813, 1135, 2020});

            values.add(new Object[]{8, "GVA", null, null, 100, null, null, 8, 702, "CASTELLON", 1817, 719, 2019});

            values.add(new Object[]{1, "GVA", null, null, 500, null, null, 1, 1502, "VALENCIA", 1803, 1505, 2019});

            values.add(new Object[]{3, "GVA", null, null, 50, null, null, 3, 352, "VALENCIA", 1807, 359, 2020});

            for (int i = 0; i < features.size(); i++) {

                for (int j = 0; j < header.length; j++) {

                    assertEquals("feature[" + i + "][" + header[j] + "]:", values.get(i)[j], features.get(i).get(header[j]));

import org.apache.commons.lang3.math.NumberUtils;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataStore;

import static org.gvsig.fmap.dal.DataStore.H2SPATIAL_PROVIDER_NAME;

import org.gvsig.fmap.dal.DatabaseWorkspaceManager;

import org.gvsig.fmap.dal.exception.ValidateDataParametersException;

import org.gvsig.fmap.dal.feature.EditableFeature;

import org.gvsig.fmap.dal.feature.EditableFeatureType;

import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters;

import org.gvsig.tools.resourcesstorage.ResourcesStorage;

import org.gvsig.tools.util.HasAFile;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

        public boolean stripStart = false;

        public boolean removeNL = false;

        private static class CommandLine {

            private final String[] cmd;

        public void parse(File f) throws Exception {

            final int SEARCHING_ITEM = 0;

            final int READING_ITEM = 1;

            boolean localNormaliceSpaces = false;

            boolean localStripStart = false;

            boolean localRemoveNL = false;

    public void removeResource(JDBCServerExplorer explorer, String storeName, String resourceName) throws Exception {

        JDBCStoreParameters storeParams = explorer.get(storeName);

        ResourcesStorage resourcesStorage = explorer.getResourcesStorage(storeParams);

    }

    public void info_h2sql(String label, File file) {

                return this.custom;

            }

            if (this.ascending) {

            }

        }

    }

    "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", 

    "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", 

    NVL2("Geometry",ST_AsBinary("Geometry"),NULL) 

-- Simple Group by

SELECT 

    NULL AS "Bool4", NULL AS "Float", NULL AS "Bool5", NULL AS "Decimal", 

    NULL AS "Geometry" 

  FROM "PUBLIC"."test" 

-- Subselect

SELECT 

    SELECT "ISO_A2" 

    FROM "countries" 

    WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND 

-- Group and Subselect

SELECT 

    FROM "countries" 

    WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND 

      ("countries"."LASTCENSUS" < 0)) LIMIT 1)),TRUE,FALSE) 

-- Simple

SELECT 

    "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", 

    "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", 

    NVL2("Geometry",ST_AsBinary("Geometry"),NULL), "ID" 

-- Computed Attribute

SELECT "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", 

    "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", 

    NVL2("Geometry",ST_AsBinary("Geometry"),NULL), 

    ("ID" * 2) AS "Compu1" 

-- Computed Attribute 2

SELECT "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", 

    NVL2("Geometry",ST_AsBinary("Geometry"),NULL), 

    ("ID" * 2) AS "Compu1", 

    (("Long" + 10) + ("ID" * 2)) AS "Compu2" 

-- Computed Extra column 1

SELECT "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", 

    NVL2("Geometry",ST_AsBinary("Geometry"),NULL), 

    ("ID" * 2) AS "Compu1", 

    (("Long" + 10) + ("ID" * 2)) AS "Extra1" 

-- Computed Extra column 2

SELECT "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", 

    NVL2("Geometry",ST_AsBinary("Geometry"),NULL), 

    ("ID" * 2) AS "Extra1", 

    (("Long" + 10) + ("ID" * 2)) AS "Extra2" 

-- Computed Extra column with where

SELECT "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", 

    NVL2("Geometry",ST_AsBinary("Geometry"),NULL), 

    ("ID" * 2) AS "Compu1", 

    (("Long" + 10) + ("ID" * 2)) AS "Extra1" 

-- Group by with computed columns and aggregate functions

SELECT MIN("test"."ID") AS "ID", MAX("test"."Byte") AS "Byte", NULL AS "Bool1", "Long", 

    SUM(((20 + "Byte") + ("ID" * 2))) AS "Extra2", 

    (("Long" + 10) + (("ID" * 2))) AS "Extra1" 

FROM "PUBLIC"."test" GROUP BY "test"."Long", (("Long" + 10) + (("ID" * 2))), (("ID" * 2)) 

   ( ((ST_GeomFromWKB(X'00000000030000000100000021402600000000000040240000000000004025f6297cff75cb40239c1d1f0e59684025d906bcf328d440233c10eaca8ab54025a9b66290ea1a4022e38c4c65197440256a09e667f3bd402295f619980c4340251c73b39ae68d402256499d6f15e64024c3ef1535754b402226f9430cd72c402463e2e0f1a698402209d683008a354024000000000000402200000000000040239c1d1f0e5968402209d683008a3540233c10eaca8ab5402226f9430cd72c4022e38c4c651974402256499d6f15e6402295f619980c43402295f619980c43402256499d6f15e64022e38c4c651974402226f9430cd72c40233c10eaca8ab5402209d683008a3540239c1d1f0e596840220000000000004024000000000000402209d683008a35402463e2e0f1a699402226f9430cd72c4024c3ef1535754c402256499d6f15e640251c73b39ae68d402295f619980c4440256a09e667f3bd4022e38c4c6519744025a9b66290ea1b40233c10eaca8ab64025d906bcf328d540239c1d1f0e59694025f6297cff75cb40240000000000014026000000000000402463e2e0f1a69a4025f6297cff75cb4024c3ef1535754d4025d906bcf328d440251c73b39ae68e4025a9b66290ea1940256a09e667f3be40256a09e667f3bb4025a9b66290ea1b40251c73b39ae68b4025d906bcf328d54024c3ef153575494025f6297cff75cb402463e2e0f1a69640260000000000004024000000000000', 4326)) && ("Geometry")) AND 

    ST_Intersects((ST_GeomFromWKB(X'00000000030000000100000021402600000000000040240000000000004025f6297cff75cb40239c1d1f0e59684025d906bcf328d440233c10eaca8ab54025a9b66290ea1a4022e38c4c65197440256a09e667f3bd402295f619980c4340251c73b39ae68d402256499d6f15e64024c3ef1535754b402226f9430cd72c402463e2e0f1a698402209d683008a354024000000000000402200000000000040239c1d1f0e5968402209d683008a3540233c10eaca8ab5402226f9430cd72c4022e38c4c651974402256499d6f15e6402295f619980c43402295f619980c43402256499d6f15e64022e38c4c651974402226f9430cd72c40233c10eaca8ab5402209d683008a3540239c1d1f0e596840220000000000004024000000000000402209d683008a35402463e2e0f1a699402226f9430cd72c4024c3ef1535754c402256499d6f15e640251c73b39ae68d402295f619980c4440256a09e667f3bd4022e38c4c6519744025a9b66290ea1b40233c10eaca8ab64025d906bcf328d540239c1d1f0e59694025f6297cff75cb40240000000000014026000000000000402463e2e0f1a69a4025f6297cff75cb4024c3ef1535754d4025d906bcf328d440251c73b39ae68e4025a9b66290ea1940256a09e667f3be40256a09e667f3bb4025a9b66290ea1b40251c73b39ae68b4025d906bcf328d54024c3ef153575494025f6297cff75cb402463e2e0f1a69640260000000000004024000000000000', 4326)),("Geometry") )) 

  ORDER BY 

  LIMIT 1;

    ) OR (

    ("Date" > DATE '2019-02-17') AND 

    ("Date" < DATE '2020-02-23')

-- Select 

SELECT NULL AS "LID_CONDUCTOR", NULL AS "ID_ACCIDENTE", NULL AS "LID_VEHICULO", NULL AS "ID_VEHICULO", NULL AS "POSIBLE_RESPONSABLE", NULL AS "FECHA_NACIMIENTO", NULL AS "SEXO", NULL AS "NACIONALIDAD", NULL AS "PAIS_RESIDENCIA", NULL AS "PROVINCIA_RESIDENCIA", NULL AS "MUNICIPIO_RESIDENCIA", NULL AS "ASISTENCIA_SANITARIA", NULL AS "INFLU_FACT_ATENCION", NULL AS "FACTORES_ATENCION", NULL AS "INFLU_PRES_ERROR", NULL AS "PRESUNTOS_ERRORES", NULL AS "CARACT_PERMISO", NULL AS "CLASE_PERMISO", NULL AS "FECHA_PERMISO", NULL AS "MOTIVO_DESPLAZAMIENTO", NULL AS "DESPLAZAMIENTO_PREVISTO", NULL AS "ACC_SEG_CINTURON", NULL AS "ACC_SEG_CASCO", NULL AS "ACC_SEG_BRAZOS", NULL AS "ACC_SEG_ESPALDA", NULL AS "ACC_SEG_TORSO", NULL AS "ACC_SEG_MANOS", NULL AS "ACC_SEG_PIERNAS", NULL AS "ACC_SEG_PIES", NULL AS "ACC_SEG_PRENDA_REF", NULL AS "INFLU_ALCOHOL", NULL AS "PRUEBA_ALCOHOLEMIA", NULL AS "TASA_ALCOHOLEMIA1", NULL AS "TASA_ALCOHOLEMIA2", NULL AS "PRUEBA_ALC_SANGRE", NULL AS "SIGNOS_INFLU_ALCOHOL", NULL AS "INFLU_DROGAS", NULL AS "PRUEBA_DROGAS", NULL AS "AMP", NULL AS "CONFIRMADO_AMP", NULL AS "BDZ", NULL AS "CONFIRMADO_BDZ", NULL AS "COC", NULL AS "CONFIRMADO_COC", NULL AS "THC", NULL AS "CONFIRMADO_THC", NULL AS "METH", NULL AS "CONFIRMADO_METH", NULL AS "OPI", NULL AS "CONFIRMADO_OPI", NULL AS "OTRAS", NULL AS "CONFIRMADO_OTRAS", NULL AS "SIGNOS_INFLU_DROGAS", NULL AS "INFLU_PRES_INFRAC_COND", NULL AS "PRES_INFRAC_COND", NULL AS "PRES_INFRAC_SIN_LUCES", NULL AS "PRES_INFRAC_SIN_TRIANGULO", NULL AS "INFLU_PRES_INFRAC_VEL", NULL AS "PRES_INFRAC_VEL_COND", NULL AS "INFLU_OTRA_INFRAC", NULL AS "OTRA_INFRAC_COND", NULL AS "EXTRA", SUM(1) AS "Acc", EXTRACT(YEAR FROM "public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE") AS "ano", "public"."ARENA2_ACCIDENTES"."COD_PROVINCIA" AS "provincia", CASE WHEN (("ASISTENCIA_SANITARIA" = 1)) THEN ('Muerto') WHEN (("ASISTENCIA_SANITARIA" = 2)) THEN ('Grave') WHEN (((((("ASISTENCIA_SANITARIA" = 3) OR ("ASISTENCIA_SANITARIA" = 4)) OR ("ASISTENCIA_SANITARIA" = 5)) OR ("ASISTENCIA_SANITARIA" = 6)) OR ("ASISTENCIA_SANITARIA" = 7))) THEN ('Leve') WHEN (("ASISTENCIA_SANITARIA" = 8)) THEN ('Ileso') ELSE ('Se Desconoce') END AS "lesividad" FROM "public"."ARENA2_CONDUCTORES" LEFT JOIN "public"."ARENA2_ACCIDENTES" ON ( ("public"."ARENA2_CONDUCTORES"."ID_ACCIDENTE") = ("public"."ARENA2_ACCIDENTES"."ID_ACCIDENTE") ) LEFT JOIN "public"."ARENA2_VEHICULOS" ON ( ("public"."ARENA2_CONDUCTORES"."LID_VEHICULO") = ("public"."ARENA2_VEHICULOS"."LID_VEHICULO") ) WHERE ((("public"."ARENA2_ACCIDENTES"."TITULARIDAD_VIA" = 2) AND ((("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 5) OR ("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 6)) OR ("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 7))) AND (("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" >= DATE '2019-01-01') AND ("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" <= DATE '2019-12-31'))) GROUP BY EXTRACT(YEAR FROM "public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE"), "public"."ARENA2_ACCIDENTES"."COD_PROVINCIA", CASE WHEN (("ASISTENCIA_SANITARIA" = 1)) THEN ('Muerto') WHEN (("ASISTENCIA_SANITARIA" = 2)) THEN ('Grave') WHEN (((((("ASISTENCIA_SANITARIA" = 3) OR ("ASISTENCIA_SANITARIA" = 4)) OR ("ASISTENCIA_SANITARIA" = 5)) OR ("ASISTENCIA_SANITARIA" = 6)) OR ("ASISTENCIA_SANITARIA" = 7))) THEN ('Leve') WHEN (("ASISTENCIA_SANITARIA" = 8)) THEN ('Ileso') ELSE ('Se Desconoce') END 

-- Count

SELECT COUNT(*) FROM ( SELECT COUNT(*) FROM "public"."ARENA2_CONDUCTORES" LEFT JOIN "public"."ARENA2_ACCIDENTES" ON ( ("public"."ARENA2_CONDUCTORES"."ID_ACCIDENTE") = ("public"."ARENA2_ACCIDENTES"."ID_ACCIDENTE") ) LEFT JOIN "public"."ARENA2_VEHICULOS" ON ( ("public"."ARENA2_CONDUCTORES"."LID_VEHICULO") = ("public"."ARENA2_VEHICULOS"."LID_VEHICULO") ) WHERE ((("public"."ARENA2_ACCIDENTES"."TITULARIDAD_VIA" = 2) AND ((("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 5) OR ("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 6)) OR ("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 7))) AND (("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" >= DATE '2019-01-01') AND ("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" <= DATE '2019-12-31'))) GROUP BY EXTRACT(YEAR FROM "public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE"), "public"."ARENA2_ACCIDENTES"."COD_PROVINCIA", CASE WHEN (("ASISTENCIA_SANITARIA" = 1)) THEN ('Muerto') WHEN (("ASISTENCIA_SANITARIA" = 2)) THEN ('Grave') WHEN (((((("ASISTENCIA_SANITARIA" = 3) OR ("ASISTENCIA_SANITARIA" = 4)) OR ("ASISTENCIA_SANITARIA" = 5)) OR ("ASISTENCIA_SANITARIA" = 6)) OR ("ASISTENCIA_SANITARIA" = 7))) THEN ('Leve') WHEN (("ASISTENCIA_SANITARIA" = 8)) THEN ('Ileso') ELSE ('Se Desconoce') END) as _subquery_alias_ ;

WHEN (("ASISTENCIA_SANITARIA" = 8)) THEN ('Ileso') 

ELSE ('Se Desconoce') 

END 

LIMIT 15;

-- Count