Revision 916 org.gvsig.oracle/trunk/org.gvsig.oracle/org.gvsig.oracle.provider/src/test/resources/org/gvsig/oracle/dal/operations/sql/resultSetForSetProvider.sql
resultSetForSetProvider.sql | ||
---|---|---|
3 | 3 |
|
4 | 4 |
-- Simple |
5 | 5 |
SELECT |
6 |
"P1"."test"."ID", "P1"."test"."Byte", "P1"."test"."Bool1", "P1"."test"."Long",
|
|
7 |
"P1"."test"."Timestamp", "P1"."test"."Date", "P1"."test"."Time",
|
|
8 |
"P1"."test"."Bool2", "P1"."test"."String", "P1"."test"."Bool3",
|
|
9 |
"P1"."test"."Double", "P1"."test"."Bool4", "P1"."test"."Float", "P1"."test"."Bool5", "P1"."test"."Decimal",
|
|
10 |
NVL2(("P1"."test"."Geometry"),("P1"."test"."Geometry").Get_WKB(),NULL)
|
|
11 |
FROM "P1"."test" ORDER BY "P1"."test"."ID" ASC;
|
|
6 |
"P1"."TEST"."ID", "P1"."TEST"."Byte", "P1"."TEST"."Bool1", "P1"."TEST"."Long",
|
|
7 |
"P1"."TEST"."Timestamp", "P1"."TEST"."Date", "P1"."TEST"."Time",
|
|
8 |
"P1"."TEST"."Bool2", "P1"."TEST"."String", "P1"."TEST"."Bool3",
|
|
9 |
"P1"."TEST"."Double", "P1"."TEST"."Bool4", "P1"."TEST"."Float", "P1"."TEST"."Bool5", "P1"."TEST"."Decimal",
|
|
10 |
NVL2(("P1"."TEST"."Geometry"),("P1"."TEST"."Geometry").Get_WKB(),NULL)
|
|
11 |
FROM "P1"."TEST" ORDER BY "P1"."TEST"."ID" ASC;
|
|
12 | 12 |
|
13 | 13 |
-- Simple Group by |
14 |
SELECT MIN("P1"."test"."ID") "ID", MAX("P1"."test"."Byte") "Byte", NULL "Bool1", "P1"."test"."Long",
|
|
14 |
SELECT MIN("P1"."TEST"."ID") "ID", MAX("P1"."TEST"."Byte") "Byte", NULL "Bool1", "P1"."TEST"."Long",
|
|
15 | 15 |
NULL "Timestamp", NULL "Date", NULL "Time", NULL "Bool2", |
16 |
NULL "String", NULL "Bool3", SUM("P1"."test"."Double") "Double",
|
|
16 |
NULL "String", NULL "Bool3", SUM("P1"."TEST"."Double") "Double",
|
|
17 | 17 |
NULL "Bool4", NULL "Float", NULL "Bool5", NULL "Decimal", |
18 | 18 |
NULL "Geometry" |
19 |
FROM "P1"."test"
|
|
20 |
GROUP BY "P1"."test"."Long"
|
|
21 |
ORDER BY "P1"."test"."Long" ASC NULLS LAST;
|
|
19 |
FROM "P1"."TEST"
|
|
20 |
GROUP BY "P1"."TEST"."Long"
|
|
21 |
ORDER BY "P1"."TEST"."Long" ASC NULLS LAST;
|
|
22 | 22 |
|
23 | 23 |
-- Subselect |
24 |
SELECT "P1"."test"."ID",
|
|
25 |
"P1"."test"."Byte",
|
|
26 |
"P1"."test"."Bool1",
|
|
27 |
"P1"."test"."Long",
|
|
28 |
"P1"."test"."Timestamp",
|
|
29 |
"P1"."test"."Date",
|
|
30 |
"P1"."test"."Time",
|
|
31 |
"P1"."test"."Bool2",
|
|
32 |
"P1"."test"."String",
|
|
33 |
"P1"."test"."Bool3",
|
|
34 |
"P1"."test"."Double",
|
|
35 |
"P1"."test"."Bool4",
|
|
36 |
"P1"."test"."Float",
|
|
37 |
"P1"."test"."Bool5",
|
|
38 |
"P1"."test"."Decimal",
|
|
39 |
NVL2(("P1"."test"."Geometry"),("P1"."test"."Geometry").Get_WKB(),NULL),
|
|
24 |
SELECT "P1"."TEST"."ID",
|
|
25 |
"P1"."TEST"."Byte",
|
|
26 |
"P1"."TEST"."Bool1",
|
|
27 |
"P1"."TEST"."Long",
|
|
28 |
"P1"."TEST"."Timestamp",
|
|
29 |
"P1"."TEST"."Date",
|
|
30 |
"P1"."TEST"."Time",
|
|
31 |
"P1"."TEST"."Bool2",
|
|
32 |
"P1"."TEST"."String",
|
|
33 |
"P1"."TEST"."Bool3",
|
|
34 |
"P1"."TEST"."Double",
|
|
35 |
"P1"."TEST"."Bool4",
|
|
36 |
"P1"."TEST"."Float",
|
|
37 |
"P1"."TEST"."Bool5",
|
|
38 |
"P1"."TEST"."Decimal",
|
|
39 |
NVL2(("P1"."TEST"."Geometry"),("P1"."TEST"."Geometry").Get_WKB(),NULL),
|
|
40 | 40 |
CASE |
41 |
WHEN (EXISTS(( SELECT "P1"."countries"."ISO_A2"
|
|
42 |
FROM "P1"."countries"
|
|
43 |
WHERE ( (( ("P1"."test"."String") = ("countries"."CONTINENT") )) AND (( ("countries"."LASTCENSUS") < (0) )) ) AND ROWNUM <= 1 ))) THEN (1)
|
|
41 |
WHEN (EXISTS(( SELECT "P1"."COUNTRIES"."ISO_A2"
|
|
42 |
FROM "P1"."COUNTRIES"
|
|
43 |
WHERE ( (( ("P1"."TEST"."String") = ("countries"."CONTINENT") )) AND (( ("countries"."LASTCENSUS") < (0) )) ) AND ROWNUM <= 1 ))) THEN (1)
|
|
44 | 44 |
ELSE (0) |
45 | 45 |
END "EXISTS62a964cd7bc24f409b97c03b9170408d" |
46 |
FROM "P1"."test"
|
|
47 |
WHERE EXISTS(( SELECT "P1"."countries"."ISO_A2"
|
|
48 |
FROM "P1"."countries"
|
|
49 |
WHERE ( (( ("P1"."test"."String") = ("countries"."CONTINENT") )) AND (( ("countries"."LASTCENSUS") < (0) )) ) AND ROWNUM <= 1 ))
|
|
50 |
ORDER BY "P1"."test"."ID" ASC;
|
|
46 |
FROM "P1"."TEST"
|
|
47 |
WHERE EXISTS(( SELECT "P1"."COUNTRIES"."ISO_A2"
|
|
48 |
FROM "P1"."COUNTRIES"
|
|
49 |
WHERE ( (( ("P1"."TEST"."String") = ("countries"."CONTINENT") )) AND (( ("countries"."LASTCENSUS") < (0) )) ) AND ROWNUM <= 1 ))
|
|
50 |
ORDER BY "P1"."TEST"."ID" ASC;
|
|
51 | 51 |
|
52 | 52 |
-- Group and Subselect |
53 |
SELECT MIN("P1"."test"."ID") "ID", MAX("P1"."test"."Byte") "Byte", NULL "Bool1", "P1"."test"."Long", NULL "Timestamp", NULL "Date", NULL "Time", NULL "Bool2", NULL "String", NULL "Bool3", SUM("P1"."test"."Double") "Double", NULL "Bool4", NULL "Float", NULL "Bool5", NULL "Decimal", NULL "Geometry" FROM "P1"."test" WHERE EXISTS(( SELECT "P1"."countries"."ISO_A2" FROM "P1"."countries" WHERE ( (( ("P1"."test"."String") = ("countries"."CONTINENT") )) AND (( ("countries"."LASTCENSUS") < (0) )) ) AND ROWNUM <= 1 )) GROUP BY "P1"."test"."Long" ORDER BY "P1"."test"."Long" ASC NULLS LAST;
|
|
53 |
SELECT MIN("P1"."TEST"."ID") "ID", MAX("P1"."TEST"."Byte") "Byte", NULL "Bool1", "P1"."TEST"."Long", NULL "Timestamp", NULL "Date", NULL "Time", NULL "Bool2", NULL "String", NULL "Bool3", SUM("P1"."TEST"."Double") "Double", NULL "Bool4", NULL "Float", NULL "Bool5", NULL "Decimal", NULL "Geometry" FROM "P1"."TEST" WHERE EXISTS(( SELECT "P1"."COUNTRIES"."ISO_A2" FROM "P1"."COUNTRIES" WHERE ( (( ("P1"."TEST"."String") = ("countries"."CONTINENT") )) AND (( ("countries"."LASTCENSUS") < (0) )) ) AND ROWNUM <= 1 )) GROUP BY "P1"."TEST"."Long" ORDER BY "P1"."TEST"."Long" ASC NULLS LAST;
|
|
54 | 54 |
|
55 | 55 |
-- Constant Column Primary Key |
56 | 56 |
SELECT |
57 |
"P1"."test"."ID",
|
|
58 |
"P1"."test"."Byte", "P1"."test"."Bool1", "P1"."test"."Long",
|
|
59 |
"P1"."test"."Timestamp", "P1"."test"."Date", "P1"."test"."Time",
|
|
60 |
"P1"."test"."Bool2", "P1"."test"."String", "P1"."test"."Bool3",
|
|
61 |
"P1"."test"."Double", "P1"."test"."Bool4", "P1"."test"."Float", "P1"."test"."Bool5", "P1"."test"."Decimal",
|
|
62 |
NVL2(("P1"."test"."Geometry"),("P1"."test"."Geometry").Get_WKB(),NULL)
|
|
63 |
FROM "P1"."test" ORDER BY "P1"."test"."ID" ASC;
|
|
57 |
"P1"."TEST"."ID",
|
|
58 |
"P1"."TEST"."Byte", "P1"."TEST"."Bool1", "P1"."TEST"."Long",
|
|
59 |
"P1"."TEST"."Timestamp", "P1"."TEST"."Date", "P1"."TEST"."Time",
|
|
60 |
"P1"."TEST"."Bool2", "P1"."TEST"."String", "P1"."TEST"."Bool3",
|
|
61 |
"P1"."TEST"."Double", "P1"."TEST"."Bool4", "P1"."TEST"."Float", "P1"."TEST"."Bool5", "P1"."TEST"."Decimal",
|
|
62 |
NVL2(("P1"."TEST"."Geometry"),("P1"."TEST"."Geometry").Get_WKB(),NULL)
|
|
63 |
FROM "P1"."TEST" ORDER BY "P1"."TEST"."ID" ASC;
|
|
64 | 64 |
|
65 | 65 |
-- CLOB |
66 | 66 |
SELECT |
67 |
"P1"."test"."ID", "P1"."test"."Byte", "P1"."test"."Bool1", "P1"."test"."Long", |
|
68 |
"P1"."test"."Timestamp", "P1"."test"."Date", "P1"."test"."Time", |
|
69 |
"P1"."test"."Bool2", "P1"."test"."String", "P1"."test"."Bool3", |
|
70 |
"P1"."test"."Double", "P1"."test"."Bool4", "P1"."test"."Float", "P1"."test"."Bool5", "P1"."test"."Decimal", |
|
71 |
NVL2(("P1"."test"."Geometry"),("P1"."test"."Geometry").Get_WKB(),NULL) |
|
72 |
FROM "P1"."test" |
|
73 |
WHERE dbms_lob.compare(("P1"."test"."String"),('hola')) = 0 ORDER BY "P1"."test"."ID" ASC; |
|
67 |
"P1"."TEST"."ID", "P1"."TEST"."Byte", "P1"."TEST"."Bool1", "P1"."TEST"."Long", |
|
68 |
"P1"."TEST"."Timestamp", "P1"."TEST"."Date", "P1"."TEST"."Time", |
|
69 |
"P1"."TEST"."Bool2", "P1"."TEST"."String", "P1"."TEST"."Bool3", |
|
70 |
"P1"."TEST"."Double", "P1"."TEST"."Bool4", "P1"."TEST"."Float", "P1"."TEST"."Bool5", "P1"."TEST"."Decimal", |
|
71 |
NVL2(("P1"."TEST"."Geometry"),("P1"."TEST"."Geometry").Get_WKB(),NULL) |
|
72 |
FROM "P1"."TEST" |
|
73 |
WHERE dbms_lob.compare(("P1"."TEST"."String"),('hola')) = 0 ORDER BY "P1"."TEST"."ID" ASC; |
Also available in: Unified diff