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 @ 46505
History | View | Annotate | Download (9.05 KB)
1 |
|
---|---|
2 |
-- ResultSetForSetProvider SQL
|
3 |
|
4 |
-- Simple
|
5 |
SELECT
|
6 |
"PUBLIC"."test"."ID", "PUBLIC"."test"."Byte", "PUBLIC"."test"."Bool1", "PUBLIC"."test"."Long", "PUBLIC"."test"."Timestamp", "PUBLIC"."test"."Date", "PUBLIC"."test"."Time", |
7 |
"PUBLIC"."test"."Bool2", "PUBLIC"."test"."String", "PUBLIC"."test"."Bool3", "PUBLIC"."test"."Double", "PUBLIC"."test"."Bool4", "PUBLIC"."test"."Float", "PUBLIC"."test"."Bool5", "PUBLIC"."test"."Decimal", |
8 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL) |
9 |
FROM "PUBLIC"."test" ORDER BY "PUBLIC"."test"."ID" ASC; |
10 |
|
11 |
-- Simple Group by
|
12 |
SELECT
|
13 |
MIN("PUBLIC"."test"."ID") AS "ID", |
14 |
MAX("PUBLIC"."test"."Byte") AS "Byte", |
15 |
NULL AS "Bool1", "PUBLIC"."test"."Long", NULL AS "Timestamp", NULL AS "Date", NULL AS "Time", NULL AS "Bool2", |
16 |
NULL AS "String", NULL AS "Bool3", |
17 |
SUM("PUBLIC"."test"."Double") AS "Double", |
18 |
NULL AS "Bool4", NULL AS "Float", NULL AS "Bool5", NULL AS "Decimal", |
19 |
NULL AS "Geometry" |
20 |
FROM "PUBLIC"."test" |
21 |
GROUP BY "PUBLIC"."test"."Long" |
22 |
ORDER BY "Long" ASC NULLS LAST; |
23 |
|
24 |
-- Subselect
|
25 |
SELECT "PUBLIC"."test"."ID", "PUBLIC"."test"."Byte", "PUBLIC"."test"."Bool1", "PUBLIC"."test"."Long", |
26 |
"PUBLIC"."test"."Timestamp", "PUBLIC"."test"."Date", "PUBLIC"."test"."Time", "PUBLIC"."test"."Bool2", |
27 |
"PUBLIC"."test"."String", "PUBLIC"."test"."Bool3", "PUBLIC"."test"."Double", "PUBLIC"."test"."Bool4", |
28 |
"PUBLIC"."test"."Float", "PUBLIC"."test"."Bool5", "PUBLIC"."test"."Decimal", |
29 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
30 |
NVL2(COALESCE( |
31 |
SELECT "countries"."ISO_A2" |
32 |
FROM "countries" |
33 |
WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) LIMIT 1 |
34 |
),TRUE,FALSE) AS "EXISTS62a964cd7bc24f409b97c03b9170408d" |
35 |
FROM "PUBLIC"."test" |
36 |
WHERE
|
37 |
NVL2(COALESCE( |
38 |
SELECT "countries"."ISO_A2" |
39 |
FROM "countries" |
40 |
WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) LIMIT 1 |
41 |
),TRUE,FALSE) |
42 |
ORDER BY "PUBLIC"."test"."ID" ASC; |
43 |
|
44 |
-- Group and Subselect
|
45 |
SELECT
|
46 |
MIN("PUBLIC"."test"."ID") AS "ID", |
47 |
MAX("PUBLIC"."test"."Byte") AS "Byte", |
48 |
NULL AS "Bool1", "PUBLIC"."test"."Long", NULL AS "Timestamp", NULL AS "Date", NULL AS "Time", |
49 |
NULL AS "Bool2", NULL AS "String", NULL AS "Bool3", |
50 |
SUM("PUBLIC"."test"."Double") AS "Double", |
51 |
NULL AS "Bool4", NULL AS "Float", NULL AS "Bool5", |
52 |
NULL AS "Decimal", NULL AS "Geometry" |
53 |
FROM "PUBLIC"."test" |
54 |
WHERE
|
55 |
NVL2(COALESCE( |
56 |
SELECT "countries"."ISO_A2" |
57 |
FROM "countries" |
58 |
WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) LIMIT 1 |
59 |
),TRUE,FALSE) |
60 |
GROUP BY "PUBLIC"."test"."Long" |
61 |
ORDER BY "Long" ASC NULLS LAST; |
62 |
|
63 |
|
64 |
-- Simple
|
65 |
SELECT
|
66 |
"PUBLIC"."test"."Byte", "PUBLIC"."test"."Bool1", "PUBLIC"."test"."Long", "PUBLIC"."test"."Timestamp", "PUBLIC"."test"."Date", "PUBLIC"."test"."Time", |
67 |
"PUBLIC"."test"."Bool2", "PUBLIC"."test"."String", "PUBLIC"."test"."Bool3", "PUBLIC"."test"."Double", "PUBLIC"."test"."Bool4", "PUBLIC"."test"."Float", "PUBLIC"."test"."Bool5", "PUBLIC"."test"."Decimal", |
68 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), "PUBLIC"."test"."ID" |
69 |
FROM "PUBLIC"."test" ORDER BY "PUBLIC"."test"."ID" ASC; |
70 |
|
71 |
-- Computed Attribute
|
72 |
SELECT "PUBLIC"."test"."ID", "PUBLIC"."test"."Byte", "PUBLIC"."test"."Bool1", "PUBLIC"."test"."Long", "PUBLIC"."test"."Timestamp", "PUBLIC"."test"."Date", "PUBLIC"."test"."Time", |
73 |
"PUBLIC"."test"."Bool2", "PUBLIC"."test"."String", "PUBLIC"."test"."Bool3", "PUBLIC"."test"."Double", "PUBLIC"."test"."Bool4", "PUBLIC"."test"."Float", "PUBLIC"."test"."Bool5", "PUBLIC"."test"."Decimal", |
74 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
75 |
("PUBLIC"."test"."ID" * 2) AS "Compu1" |
76 |
FROM "PUBLIC"."test" ORDER BY "PUBLIC"."test"."ID" ASC; |
77 |
|
78 |
-- Computed Attribute 2
|
79 |
SELECT "PUBLIC"."test"."ID", "PUBLIC"."test"."Byte", "PUBLIC"."test"."Bool1", "PUBLIC"."test"."Long", "PUBLIC"."test"."Timestamp", "PUBLIC"."test"."Date", "PUBLIC"."test"."Time", |
80 |
"PUBLIC"."test"."Bool2", "PUBLIC"."test"."String", "PUBLIC"."test"."Bool3", "PUBLIC"."test"."Double", "PUBLIC"."test"."Bool4", "PUBLIC"."test"."Float", "PUBLIC"."test"."Bool5", "PUBLIC"."test"."Decimal", |
81 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
82 |
("PUBLIC"."test"."ID" * 2) AS "Compu1", |
83 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) AS "Compu2" |
84 |
FROM "PUBLIC"."test" ORDER BY "PUBLIC"."test"."ID" ASC; |
85 |
|
86 |
-- Computed Extra column 1
|
87 |
SELECT "PUBLIC"."test"."ID", "PUBLIC"."test"."Byte", "PUBLIC"."test"."Bool1", "PUBLIC"."test"."Long", "PUBLIC"."test"."Timestamp", "PUBLIC"."test"."Date", "PUBLIC"."test"."Time", |
88 |
"PUBLIC"."test"."Bool2", "PUBLIC"."test"."String", "PUBLIC"."test"."Bool3", "PUBLIC"."test"."Double", "PUBLIC"."test"."Bool4", "PUBLIC"."test"."Float", "PUBLIC"."test"."Bool5", "PUBLIC"."test"."Decimal", |
89 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
90 |
("PUBLIC"."test"."ID" * 2) AS "Compu1", |
91 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) AS "Extra1" |
92 |
FROM "PUBLIC"."test" ORDER BY "PUBLIC"."test"."ID" ASC; |
93 |
|
94 |
-- Computed Extra column 2
|
95 |
SELECT "PUBLIC"."test"."ID", "PUBLIC"."test"."Byte", "PUBLIC"."test"."Bool1", "PUBLIC"."test"."Long", "PUBLIC"."test"."Timestamp", "PUBLIC"."test"."Date", "PUBLIC"."test"."Time", |
96 |
"PUBLIC"."test"."Bool2", "PUBLIC"."test"."String", "PUBLIC"."test"."Bool3", "PUBLIC"."test"."Double", "PUBLIC"."test"."Bool4", "PUBLIC"."test"."Float", "PUBLIC"."test"."Bool5", "PUBLIC"."test"."Decimal", |
97 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
98 |
("PUBLIC"."test"."ID" * 2) AS "Extra1", |
99 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) AS "Extra2" |
100 |
FROM "PUBLIC"."test" ORDER BY "PUBLIC"."test"."ID" ASC; |
101 |
|
102 |
-- Computed Extra column with where
|
103 |
SELECT "PUBLIC"."test"."ID", "PUBLIC"."test"."Byte", "PUBLIC"."test"."Bool1", "PUBLIC"."test"."Long", "PUBLIC"."test"."Timestamp", "PUBLIC"."test"."Date", "PUBLIC"."test"."Time", |
104 |
"PUBLIC"."test"."Bool2", "PUBLIC"."test"."String", "PUBLIC"."test"."Bool3", "PUBLIC"."test"."Double", "PUBLIC"."test"."Bool4", "PUBLIC"."test"."Float", "PUBLIC"."test"."Bool5", "PUBLIC"."test"."Decimal", |
105 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
106 |
("PUBLIC"."test"."ID" * 2) AS "Compu1", |
107 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) AS "Extra1" |
108 |
FROM "PUBLIC"."test" WHERE ((("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) > 10) ORDER BY "Extra1" ASC NULLS LAST, "PUBLIC"."test"."ID" ASC; |
109 |
|
110 |
-- Group by with computed columns and aggregate functions
|
111 |
SELECT
|
112 |
MIN("PUBLIC"."test"."ID") AS "ID", MAX("PUBLIC"."test"."Byte") AS "Byte", NULL AS "Bool1", "PUBLIC"."test"."Long", |
113 |
NULL AS "Timestamp", NULL AS "Date", NULL AS "Time", NULL AS "Bool2", NULL AS "String", NULL AS "Bool3", |
114 |
SUM("PUBLIC"."test"."Double") AS "Double", NULL AS "Bool4", NULL AS "Float", NULL AS "Bool5", NULL AS "Decimal", |
115 |
NULL AS "Geometry", |
116 |
("ID" * 2) AS "Compu1", |
117 |
SUM(("PUBLIC"."test"."Long" + 300)) AS "Compu2", |
118 |
SUM(1) AS "Compu3", |
119 |
(("Long" + 10) + (("ID" * 2))) AS "Extra1", |
120 |
SUM(((20 + "PUBLIC"."test"."Byte") + ("PUBLIC"."test"."ID" * 2))) AS "Extra2" |
121 |
FROM "PUBLIC"."test" |
122 |
GROUP BY "PUBLIC"."test"."Long", (("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)), ("PUBLIC"."test"."ID" * 2) |
123 |
ORDER BY "Long" ASC NULLS LAST, "Extra1" ASC NULLS LAST, "Compu1" ASC NULLS LAST; |
124 |
|
125 |
-- Group by and order by with computed columns and aggregate functions
|
126 |
SELECT
|
127 |
MIN("PUBLIC"."test"."ID") AS "ID", MAX("PUBLIC"."test"."Byte") AS "Byte", NULL AS "Bool1", "PUBLIC"."test"."Long", |
128 |
NULL AS "Timestamp", NULL AS "Date", NULL AS "Time", NULL AS "Bool2", NULL AS "String", NULL AS "Bool3", |
129 |
SUM("PUBLIC"."test"."Double") AS "Double", NULL AS "Bool4", NULL AS "Float", NULL AS "Bool5", NULL AS "Decimal", |
130 |
NULL AS "Geometry", |
131 |
("ID" * 2) AS "Compu1", |
132 |
SUM(("PUBLIC"."test"."Long" + 300)) AS "Compu2", |
133 |
SUM(1) AS "Compu3", |
134 |
(("Long" + 10) + (("ID" * 2))) AS "Extra1", |
135 |
SUM(((20 + "PUBLIC"."test"."Byte") + ("PUBLIC"."test"."ID" * 2))) AS "Extra2", |
136 |
NULL AS "Extra3" |
137 |
FROM "PUBLIC"."test" |
138 |
GROUP BY "PUBLIC"."test"."Long", (("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)), ("PUBLIC"."test"."ID" * 2) |
139 |
ORDER BY "Extra1" ASC NULLS LAST, "Extra2" ASC NULLS LAST, (("Long" + 33)) ASC NULLS LAST; |
140 |
|
141 |
-- Simple Aggregate and Order
|
142 |
SELECT
|
143 |
MIN("PUBLIC"."test"."ID") AS "ID", |
144 |
MAX("PUBLIC"."test"."Byte") AS "Byte", |
145 |
NULL AS "Bool1", NULL AS "Long", NULL AS "Timestamp", NULL AS "Date", NULL AS "Time", NULL AS "Bool2", |
146 |
NULL AS "String", NULL AS "Bool3", |
147 |
SUM("PUBLIC"."test"."Double") AS "Double", |
148 |
NULL AS "Bool4", NULL AS "Float", NULL AS "Bool5", NULL AS "Decimal", |
149 |
NULL AS "Geometry", |
150 |
SUM(("PUBLIC"."test"."Long" + 10)) AS "Extra1", |
151 |
NULL AS "Extra2" |
152 |
FROM "PUBLIC"."test" |
153 |
ORDER BY |
154 |
"ID" ASC, "Long" ASC NULLS LAST, |
155 |
"Extra1" ASC NULLS LAST, |
156 |
((20 + "Byte")) ASC NULLS LAST; |