svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.plugin / org.gvsig.h2spatial / org.gvsig.h2spatial.h2gis132 / org.gvsig.h2spatial.h2gis132.provider / src / test / resources / org / gvsig / fmap / dal / store / h2 / resultSetForSetProvider.sql @ 46105
History | View | Annotate | Download (4.67 KB)
| 1 | 45472 | jjdelcerro | |
|---|---|---|---|
| 2 | -- ResultSetForSetProvider SQL
|
||
| 3 | |||
| 4 | -- Simple
|
||
| 5 | SELECT
|
||
| 6 | "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", |
||
| 7 | "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", |
||
| 8 | NVL2("Geometry",ST_AsBinary("Geometry"),NULL) |
||
| 9 | 46014 | jjdelcerro | FROM "PUBLIC"."test" ORDER BY "ID" ASC; |
| 10 | 45472 | jjdelcerro | |
| 11 | -- Simple Group by
|
||
| 12 | SELECT
|
||
| 13 | MIN("test"."ID") AS "ID", MAX("test"."Byte") AS "Byte", NULL AS "Bool1", "Long", |
||
| 14 | NULL AS "Timestamp", NULL AS "Date", NULL AS "Time", NULL AS "Bool2", |
||
| 15 | NULL AS "String", NULL AS "Bool3", SUM("test"."Double") AS "Double", |
||
| 16 | NULL AS "Bool4", NULL AS "Float", NULL AS "Bool5", NULL AS "Decimal", |
||
| 17 | NULL AS "Geometry" |
||
| 18 | FROM "PUBLIC"."test" |
||
| 19 | 46105 | omartinez | GROUP BY "test"."Long" ORDER BY "test"."Long" ASC, "ID" ASC; |
| 20 | 45472 | jjdelcerro | |
| 21 | -- Subselect
|
||
| 22 | SELECT
|
||
| 23 | "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", "Bool2", |
||
| 24 | "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", |
||
| 25 | NVL2("Geometry",ST_AsBinary("Geometry"),NULL), |
||
| 26 | NVL2(COALESCE(( |
||
| 27 | SELECT "ISO_A2" |
||
| 28 | FROM "countries" |
||
| 29 | WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND |
||
| 30 | ("countries"."LASTCENSUS" < 0)) LIMIT 1)),TRUE,FALSE) |
||
| 31 | AS "EXISTS62a964cd7bc24f409b97c03b9170408d" |
||
| 32 | FROM "PUBLIC"."test" |
||
| 33 | WHERE NVL2(COALESCE((
|
||
| 34 | SELECT "ISO_A2" |
||
| 35 | FROM "countries" |
||
| 36 | WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND |
||
| 37 | 46014 | jjdelcerro | ("countries"."LASTCENSUS" < 0)) LIMIT 1)),TRUE,FALSE) ORDER BY "ID" ASC; |
| 38 | 45472 | jjdelcerro | |
| 39 | -- Group and Subselect
|
||
| 40 | SELECT
|
||
| 41 | MIN("test"."ID") AS "ID", MAX("test"."Byte") AS "Byte", NULL AS "Bool1", "Long", |
||
| 42 | NULL AS "Timestamp", NULL AS "Date", NULL AS "Time", NULL AS "Bool2", |
||
| 43 | NULL AS "String", NULL AS "Bool3", SUM("test"."Double") AS "Double", |
||
| 44 | NULL AS "Bool4", NULL AS "Float", NULL AS "Bool5", NULL AS "Decimal", |
||
| 45 | NULL AS "Geometry" |
||
| 46 | FROM "PUBLIC"."test" |
||
| 47 | WHERE NVL2(COALESCE((
|
||
| 48 | SELECT "ISO_A2" |
||
| 49 | FROM "countries" |
||
| 50 | WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND |
||
| 51 | ("countries"."LASTCENSUS" < 0)) LIMIT 1)),TRUE,FALSE) |
||
| 52 | 46105 | omartinez | GROUP BY "test"."Long" ORDER BY "test"."Long" ASC, "ID" ASC; |
| 53 | 45472 | jjdelcerro | |
| 54 | -- Simple
|
||
| 55 | SELECT
|
||
| 56 | "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", |
||
| 57 | "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", |
||
| 58 | NVL2("Geometry",ST_AsBinary("Geometry"),NULL), "ID" |
||
| 59 | 46014 | jjdelcerro | FROM "PUBLIC"."test" ORDER BY "ID" ASC; |
| 60 | 46050 | omartinez | |
| 61 | -- Computed Attribute
|
||
| 62 | SELECT "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", |
||
| 63 | "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", |
||
| 64 | NVL2("Geometry",ST_AsBinary("Geometry"),NULL), |
||
| 65 | ("ID" * 2) AS "Compu1" |
||
| 66 | FROM "PUBLIC"."test" ORDER BY "ID" ASC; |
||
| 67 | |||
| 68 | -- Computed Attribute 2
|
||
| 69 | SELECT "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", |
||
| 70 | "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", |
||
| 71 | NVL2("Geometry",ST_AsBinary("Geometry"),NULL), |
||
| 72 | ("ID" * 2) AS "Compu1", |
||
| 73 | 46104 | omartinez | (("Long" + 10) + ("ID" * 2)) AS "Compu2" |
| 74 | 46050 | omartinez | FROM "PUBLIC"."test" ORDER BY "ID" ASC; |
| 75 | |||
| 76 | -- Computed Extra column 1
|
||
| 77 | SELECT "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", |
||
| 78 | "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", |
||
| 79 | NVL2("Geometry",ST_AsBinary("Geometry"),NULL), |
||
| 80 | ("ID" * 2) AS "Compu1", |
||
| 81 | 46104 | omartinez | (("Long" + 10) + ("ID" * 2)) AS "Extra1" |
| 82 | 46050 | omartinez | FROM "PUBLIC"."test" ORDER BY "ID" ASC; |
| 83 | |||
| 84 | -- Computed Extra column 2
|
||
| 85 | SELECT "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", |
||
| 86 | "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", |
||
| 87 | NVL2("Geometry",ST_AsBinary("Geometry"),NULL), |
||
| 88 | ("ID" * 2) AS "Extra1", |
||
| 89 | 46104 | omartinez | (("Long" + 10) + ("ID" * 2)) AS "Extra2" |
| 90 | 46050 | omartinez | FROM "PUBLIC"."test" ORDER BY "ID" ASC; |
| 91 | |||
| 92 | -- Computed Extra column with where
|
||
| 93 | SELECT "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", |
||
| 94 | "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", |
||
| 95 | NVL2("Geometry",ST_AsBinary("Geometry"),NULL), |
||
| 96 | ("ID" * 2) AS "Compu1", |
||
| 97 | 46104 | omartinez | (("Long" + 10) + ("ID" * 2)) AS "Extra1" |
| 98 | FROM "PUBLIC"."test" WHERE ((("Long" + 10) + (("ID" * 2))) > 10) ORDER BY "Extra1" ASC, "ID" ASC; |
||
| 99 | 46050 | omartinez | |
| 100 | -- Group by with computed columns and aggregate functions
|
||
| 101 | SELECT MIN("test"."ID") AS "ID", MAX("test"."Byte") AS "Byte", NULL AS "Bool1", "Long", |
||
| 102 | NULL AS "Timestamp", NULL AS "Date", NULL AS "Time", NULL AS "Bool2", |
||
| 103 | NULL AS "String", NULL AS "Bool3", SUM("test"."Double") AS "Double", |
||
| 104 | NULL AS "Bool4", NULL AS "Float", NULL AS "Bool5", NULL AS "Decimal", |
||
| 105 | NULL AS "Geometry", |
||
| 106 | ("ID" * 2) AS "Compu1", |
||
| 107 | SUM(("Long" + 300)) AS "Compu2", |
||
| 108 | 46104 | omartinez | SUM(((20 + "Byte") + ("ID" * 2))) AS "Extra2", |
| 109 | 46050 | omartinez | (("Long" + 10) + (("ID" * 2))) AS "Extra1" |
| 110 | 46105 | omartinez | FROM "PUBLIC"."test" GROUP BY "test"."Long", (("Long" + 10) + (("ID" * 2))), (("ID" * 2)) |
| 111 | ORDER BY "test"."Long" ASC, "Extra1" ASC, "Compu1" ASC, "ID" ASC; |