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 @ 46517
History | View | Annotate | Download (12 KB)
1 |
|
---|---|
2 |
-- rem ResultSetForSetProvider SQL
|
3 |
|
4 |
-- normalize-spaces true
|
5 |
-- strip-start true
|
6 |
-- remove-nl true
|
7 |
-- replace-nl-by-space true
|
8 |
-- trim-end true
|
9 |
|
10 |
-- begin testSimple
|
11 |
SELECT "PUBLIC"."test"."ID", |
12 |
"PUBLIC"."test"."Byte", |
13 |
"PUBLIC"."test"."Bool1", |
14 |
"PUBLIC"."test"."Long", |
15 |
"PUBLIC"."test"."Timestamp", |
16 |
"PUBLIC"."test"."Date", |
17 |
"PUBLIC"."test"."Time", |
18 |
"PUBLIC"."test"."Bool2", |
19 |
"PUBLIC"."test"."String", |
20 |
"PUBLIC"."test"."Bool3", |
21 |
"PUBLIC"."test"."Double", |
22 |
"PUBLIC"."test"."Bool4", |
23 |
"PUBLIC"."test"."Float", |
24 |
"PUBLIC"."test"."Bool5", |
25 |
"PUBLIC"."test"."Decimal", |
26 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL) |
27 |
FROM "PUBLIC"."test" |
28 |
ORDER BY "PUBLIC"."test"."ID" ASC |
29 |
-- end testSimple
|
30 |
|
31 |
-- begin testSimpleGroup
|
32 |
SELECT MIN("PUBLIC"."test"."ID") AS "ID", |
33 |
MAX("PUBLIC"."test"."Byte") AS "Byte", |
34 |
NULL AS "Bool1", |
35 |
"PUBLIC"."test"."Long", |
36 |
NULL AS "Timestamp", |
37 |
NULL AS "Date", |
38 |
NULL AS "Time", |
39 |
NULL AS "Bool2", |
40 |
NULL AS "String", |
41 |
NULL AS "Bool3", |
42 |
SUM("PUBLIC"."test"."Double") AS "Double", |
43 |
NULL AS "Bool4", |
44 |
NULL AS "Float", |
45 |
NULL AS "Bool5", |
46 |
NULL AS "Decimal", |
47 |
NULL AS "Geometry" |
48 |
FROM "PUBLIC"."test" |
49 |
GROUP BY "PUBLIC"."test"."Long" |
50 |
ORDER BY "PUBLIC"."test"."Long" ASC NULLS LAST |
51 |
-- end testSimpleGroup
|
52 |
|
53 |
-- begin testSubselect
|
54 |
SELECT "PUBLIC"."test"."ID", |
55 |
"PUBLIC"."test"."Byte", |
56 |
"PUBLIC"."test"."Bool1", |
57 |
"PUBLIC"."test"."Long", |
58 |
"PUBLIC"."test"."Timestamp", |
59 |
"PUBLIC"."test"."Date", |
60 |
"PUBLIC"."test"."Time", |
61 |
"PUBLIC"."test"."Bool2", |
62 |
"PUBLIC"."test"."String", |
63 |
"PUBLIC"."test"."Bool3", |
64 |
"PUBLIC"."test"."Double", |
65 |
"PUBLIC"."test"."Bool4", |
66 |
"PUBLIC"."test"."Float", |
67 |
"PUBLIC"."test"."Bool5", |
68 |
"PUBLIC"."test"."Decimal", |
69 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
70 |
NVL2(COALESCE(SELECT "countries"."ISO_A2" FROM "countries" WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) LIMIT 1),TRUE,FALSE) AS "EXISTS62a964cd7bc24f409b97c03b9170408d" |
71 |
FROM "PUBLIC"."test" |
72 |
WHERE NVL2(COALESCE(SELECT "countries"."ISO_A2" |
73 |
FROM "countries" |
74 |
WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) |
75 |
LIMIT 1),TRUE,FALSE) |
76 |
ORDER BY "PUBLIC"."test"."ID" ASC |
77 |
-- end testSubselect
|
78 |
|
79 |
-- begin testSubselect2
|
80 |
SELECT "PUBLIC"."test"."ID", |
81 |
"PUBLIC"."test"."Byte", |
82 |
"PUBLIC"."test"."Bool1", |
83 |
"PUBLIC"."test"."Long", |
84 |
"PUBLIC"."test"."Timestamp", |
85 |
"PUBLIC"."test"."Date", |
86 |
"PUBLIC"."test"."Time", |
87 |
"PUBLIC"."test"."Bool2", |
88 |
"PUBLIC"."test"."String", |
89 |
"PUBLIC"."test"."Bool3", |
90 |
"PUBLIC"."test"."Double", |
91 |
"PUBLIC"."test"."Bool4", |
92 |
"PUBLIC"."test"."Float", |
93 |
"PUBLIC"."test"."Bool5", |
94 |
"PUBLIC"."test"."Decimal", |
95 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
96 |
NVL2(COALESCE(SELECT "PUBLIC"."test"."Long" FROM "countries" WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) LIMIT 1),TRUE,FALSE) AS "EXISTS62a964cd7bc24f409b97c03b9170408d" |
97 |
FROM "PUBLIC"."test" |
98 |
WHERE NVL2(COALESCE(SELECT "PUBLIC"."test"."Long" |
99 |
FROM "countries" |
100 |
WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) |
101 |
LIMIT 1),TRUE,FALSE) |
102 |
ORDER BY "PUBLIC"."test"."ID" ASC |
103 |
-- end testSubselect2
|
104 |
|
105 |
-- begin testGroupAndSubselect
|
106 |
SELECT MIN("PUBLIC"."test"."ID") AS "ID", |
107 |
MAX("PUBLIC"."test"."Byte") AS "Byte", |
108 |
NULL AS "Bool1", |
109 |
"PUBLIC"."test"."Long", |
110 |
NULL AS "Timestamp", |
111 |
NULL AS "Date", |
112 |
NULL AS "Time", |
113 |
NULL AS "Bool2", |
114 |
NULL AS "String", |
115 |
NULL AS "Bool3", |
116 |
SUM("PUBLIC"."test"."Double") AS "Double", |
117 |
NULL AS "Bool4", |
118 |
NULL AS "Float", |
119 |
NULL AS "Bool5", |
120 |
NULL AS "Decimal", |
121 |
NULL AS "Geometry" |
122 |
FROM "PUBLIC"."test" |
123 |
WHERE NVL2(COALESCE(SELECT "countries"."ISO_A2" |
124 |
FROM "countries" |
125 |
WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND ("countries"."LASTCENSUS" < 0)) |
126 |
LIMIT 1),TRUE,FALSE) |
127 |
GROUP BY "PUBLIC"."test"."Long" |
128 |
ORDER BY "PUBLIC"."test"."Long" ASC NULLS LAST |
129 |
-- end testGroupAndSubselect
|
130 |
|
131 |
-- begin testConstantColumnPrimaryKey
|
132 |
SELECT "PUBLIC"."test"."Byte", |
133 |
"PUBLIC"."test"."Bool1", |
134 |
"PUBLIC"."test"."Long", |
135 |
"PUBLIC"."test"."Timestamp", |
136 |
"PUBLIC"."test"."Date", |
137 |
"PUBLIC"."test"."Time", |
138 |
"PUBLIC"."test"."Bool2", |
139 |
"PUBLIC"."test"."String", |
140 |
"PUBLIC"."test"."Bool3", |
141 |
"PUBLIC"."test"."Double", |
142 |
"PUBLIC"."test"."Bool4", |
143 |
"PUBLIC"."test"."Float", |
144 |
"PUBLIC"."test"."Bool5", |
145 |
"PUBLIC"."test"."Decimal", |
146 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
147 |
"PUBLIC"."test"."ID" |
148 |
FROM "PUBLIC"."test" |
149 |
ORDER BY "PUBLIC"."test"."ID" ASC |
150 |
-- end testConstantColumnPrimaryKey
|
151 |
|
152 |
-- begin testComputedAttribute
|
153 |
SELECT "PUBLIC"."test"."ID", |
154 |
"PUBLIC"."test"."Byte", |
155 |
"PUBLIC"."test"."Bool1", |
156 |
"PUBLIC"."test"."Long", |
157 |
"PUBLIC"."test"."Timestamp", |
158 |
"PUBLIC"."test"."Date", |
159 |
"PUBLIC"."test"."Time", |
160 |
"PUBLIC"."test"."Bool2", |
161 |
"PUBLIC"."test"."String", |
162 |
"PUBLIC"."test"."Bool3", |
163 |
"PUBLIC"."test"."Double", |
164 |
"PUBLIC"."test"."Bool4", |
165 |
"PUBLIC"."test"."Float", |
166 |
"PUBLIC"."test"."Bool5", |
167 |
"PUBLIC"."test"."Decimal", |
168 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
169 |
("PUBLIC"."test"."ID" * 2) AS "Compu1" |
170 |
FROM "PUBLIC"."test" |
171 |
ORDER BY "PUBLIC"."test"."ID" ASC |
172 |
-- end testComputedAttribute
|
173 |
|
174 |
-- begin testComputedAttribute2
|
175 |
SELECT "PUBLIC"."test"."ID", |
176 |
"PUBLIC"."test"."Byte", |
177 |
"PUBLIC"."test"."Bool1", |
178 |
"PUBLIC"."test"."Long", |
179 |
"PUBLIC"."test"."Timestamp", |
180 |
"PUBLIC"."test"."Date", |
181 |
"PUBLIC"."test"."Time", |
182 |
"PUBLIC"."test"."Bool2", |
183 |
"PUBLIC"."test"."String", |
184 |
"PUBLIC"."test"."Bool3", |
185 |
"PUBLIC"."test"."Double", |
186 |
"PUBLIC"."test"."Bool4", |
187 |
"PUBLIC"."test"."Float", |
188 |
"PUBLIC"."test"."Bool5", |
189 |
"PUBLIC"."test"."Decimal", |
190 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
191 |
("PUBLIC"."test"."ID" * 2) AS "Compu1", |
192 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) AS "Compu2" |
193 |
FROM "PUBLIC"."test" |
194 |
ORDER BY "PUBLIC"."test"."ID" ASC |
195 |
-- end testComputedAttribute2
|
196 |
|
197 |
-- begin testComputedExtraColumn
|
198 |
SELECT "PUBLIC"."test"."ID", |
199 |
"PUBLIC"."test"."Byte", |
200 |
"PUBLIC"."test"."Bool1", |
201 |
"PUBLIC"."test"."Long", |
202 |
"PUBLIC"."test"."Timestamp", |
203 |
"PUBLIC"."test"."Date", |
204 |
"PUBLIC"."test"."Time", |
205 |
"PUBLIC"."test"."Bool2", |
206 |
"PUBLIC"."test"."String", |
207 |
"PUBLIC"."test"."Bool3", |
208 |
"PUBLIC"."test"."Double", |
209 |
"PUBLIC"."test"."Bool4", |
210 |
"PUBLIC"."test"."Float", |
211 |
"PUBLIC"."test"."Bool5", |
212 |
"PUBLIC"."test"."Decimal", |
213 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
214 |
("PUBLIC"."test"."ID" * 2) AS "Compu1", |
215 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) AS "Extra1" |
216 |
FROM "PUBLIC"."test" |
217 |
ORDER BY "PUBLIC"."test"."ID" ASC |
218 |
-- end testComputedExtraColumn
|
219 |
|
220 |
-- begin testComputedExtraColumn2
|
221 |
SELECT "PUBLIC"."test"."ID", |
222 |
"PUBLIC"."test"."Byte", |
223 |
"PUBLIC"."test"."Bool1", |
224 |
"PUBLIC"."test"."Long", |
225 |
"PUBLIC"."test"."Timestamp", |
226 |
"PUBLIC"."test"."Date", |
227 |
"PUBLIC"."test"."Time", |
228 |
"PUBLIC"."test"."Bool2", |
229 |
"PUBLIC"."test"."String", |
230 |
"PUBLIC"."test"."Bool3", |
231 |
"PUBLIC"."test"."Double", |
232 |
"PUBLIC"."test"."Bool4", |
233 |
"PUBLIC"."test"."Float", |
234 |
"PUBLIC"."test"."Bool5", |
235 |
"PUBLIC"."test"."Decimal", |
236 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
237 |
("PUBLIC"."test"."ID" * 2) AS "Extra1", |
238 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) AS "Extra2" |
239 |
FROM "PUBLIC"."test" |
240 |
ORDER BY "PUBLIC"."test"."ID" ASC |
241 |
-- end testComputedExtraColumn2
|
242 |
|
243 |
-- begin testComputedExtraColumnWithWhere
|
244 |
SELECT "PUBLIC"."test"."ID", |
245 |
"PUBLIC"."test"."Byte", |
246 |
"PUBLIC"."test"."Bool1", |
247 |
"PUBLIC"."test"."Long", |
248 |
"PUBLIC"."test"."Timestamp", |
249 |
"PUBLIC"."test"."Date", |
250 |
"PUBLIC"."test"."Time", |
251 |
"PUBLIC"."test"."Bool2", |
252 |
"PUBLIC"."test"."String", |
253 |
"PUBLIC"."test"."Bool3", |
254 |
"PUBLIC"."test"."Double", |
255 |
"PUBLIC"."test"."Bool4", |
256 |
"PUBLIC"."test"."Float", |
257 |
"PUBLIC"."test"."Bool5", |
258 |
"PUBLIC"."test"."Decimal", |
259 |
NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL), |
260 |
("PUBLIC"."test"."ID" * 2) AS "Compu1", |
261 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) AS "Extra1" |
262 |
FROM "PUBLIC"."test" |
263 |
WHERE ((("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) > 10) |
264 |
ORDER BY "Extra1" ASC NULLS LAST, |
265 |
"PUBLIC"."test"."ID" ASC |
266 |
-- end testComputedExtraColumnWithWhere
|
267 |
|
268 |
-- begin testGroupByComputed
|
269 |
SELECT MIN("PUBLIC"."test"."ID") AS "ID", |
270 |
MAX("PUBLIC"."test"."Byte") AS "Byte", |
271 |
NULL AS "Bool1", |
272 |
"PUBLIC"."test"."Long", |
273 |
NULL AS "Timestamp", |
274 |
NULL AS "Date", |
275 |
NULL AS "Time", |
276 |
NULL AS "Bool2", |
277 |
NULL AS "String", |
278 |
NULL AS "Bool3", |
279 |
SUM("PUBLIC"."test"."Double") AS "Double", |
280 |
NULL AS "Bool4", |
281 |
NULL AS "Float", |
282 |
NULL AS "Bool5", |
283 |
NULL AS "Decimal", |
284 |
NULL AS "Geometry", |
285 |
("PUBLIC"."test"."ID" * 2) AS "Compu1", |
286 |
SUM(("PUBLIC"."test"."Long" + 300)) AS "Compu2", |
287 |
SUM(1) AS "Compu3", |
288 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) AS "Extra1", |
289 |
SUM(((20 + "PUBLIC"."test"."Byte") + ("PUBLIC"."test"."ID" * 2))) AS "Extra2" |
290 |
FROM "PUBLIC"."test" |
291 |
GROUP BY "PUBLIC"."test"."Long", |
292 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)), |
293 |
("PUBLIC"."test"."ID" * 2) |
294 |
ORDER BY "PUBLIC"."test"."Long" ASC NULLS LAST, |
295 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) ASC NULLS LAST, |
296 |
("PUBLIC"."test"."ID" * 2) ASC NULLS LAST |
297 |
-- end testGroupByComputed
|
298 |
|
299 |
-- begin testGroupByAndOrderByComputed
|
300 |
SELECT MIN("PUBLIC"."test"."ID") AS "ID", |
301 |
MAX("PUBLIC"."test"."Byte") AS "Byte", |
302 |
NULL AS "Bool1", |
303 |
"PUBLIC"."test"."Long", |
304 |
NULL AS "Timestamp", |
305 |
NULL AS "Date", |
306 |
NULL AS "Time", |
307 |
NULL AS "Bool2", |
308 |
NULL AS "String", |
309 |
NULL AS "Bool3", |
310 |
SUM("PUBLIC"."test"."Double") AS "Double", |
311 |
NULL AS "Bool4", |
312 |
NULL AS "Float", |
313 |
NULL AS "Bool5", |
314 |
NULL AS "Decimal", |
315 |
NULL AS "Geometry", |
316 |
("PUBLIC"."test"."ID" * 2) AS "Compu1", |
317 |
SUM(("PUBLIC"."test"."Long" + 300)) AS "Compu2", |
318 |
SUM(1) AS "Compu3", |
319 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)) AS "Extra1", |
320 |
SUM(((20 + "PUBLIC"."test"."Byte") + ("PUBLIC"."test"."ID" * 2))) AS "Extra2", |
321 |
NULL AS "Extra3" |
322 |
FROM "PUBLIC"."test" |
323 |
GROUP BY "PUBLIC"."test"."Long", |
324 |
(("PUBLIC"."test"."Long" + 10) + ("PUBLIC"."test"."ID" * 2)), |
325 |
("PUBLIC"."test"."ID" * 2) |
326 |
ORDER BY (("Long" + 10) + ("ID" * 2)) ASC NULLS LAST, |
327 |
SUM(((20 + "Byte") + ("ID" * 2))) ASC NULLS LAST |
328 |
-- end testGroupByAndOrderByComputed
|
329 |
|
330 |
-- begin testSimpleAggregateAndOrder
|
331 |
SELECT MIN("PUBLIC"."test"."ID") AS "ID", |
332 |
MAX("PUBLIC"."test"."Byte") AS "Byte", |
333 |
NULL AS "Bool1", |
334 |
NULL AS "Long", |
335 |
NULL AS "Timestamp", |
336 |
NULL AS "Date", |
337 |
NULL AS "Time", |
338 |
NULL AS "Bool2", |
339 |
NULL AS "String", |
340 |
NULL AS "Bool3", |
341 |
SUM("PUBLIC"."test"."Double") AS "Double", |
342 |
NULL AS "Bool4", |
343 |
NULL AS "Float", |
344 |
NULL AS "Bool5", |
345 |
NULL AS "Decimal", |
346 |
NULL AS "Geometry", |
347 |
SUM(("PUBLIC"."test"."Long" + 10)) AS "Extra1", |
348 |
NULL AS "Extra2" |
349 |
FROM "PUBLIC"."test" |
350 |
ORDER BY MIN("PUBLIC"."test"."ID") ASC, |
351 |
SUM(("Long" + 10)) ASC NULLS LAST |
352 |
-- end testSimpleAggregateAndOrder
|