gvsig-oracle / org.gvsig.oracle / trunk / org.gvsig.oracle / org.gvsig.oracle.provider / src / test / resources / org / gvsig / oracle / dal / operations / sql / resultSetForSetProvider.sql @ 916
History | View | Annotate | Download (3.69 KB)
| 1 |
|
|---|---|
| 2 |
-- ResultSetForSetProvider SQL
|
| 3 |
|
| 4 |
-- Simple
|
| 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; |
| 12 |
|
| 13 |
-- Simple Group by
|
| 14 |
SELECT MIN("P1"."TEST"."ID") "ID", MAX("P1"."TEST"."Byte") "Byte", NULL "Bool1", "P1"."TEST"."Long", |
| 15 |
NULL "Timestamp", NULL "Date", NULL "Time", NULL "Bool2", |
| 16 |
NULL "String", NULL "Bool3", SUM("P1"."TEST"."Double") "Double", |
| 17 |
NULL "Bool4", NULL "Float", NULL "Bool5", NULL "Decimal", |
| 18 |
NULL "Geometry" |
| 19 |
FROM "P1"."TEST" |
| 20 |
GROUP BY "P1"."TEST"."Long" |
| 21 |
ORDER BY "P1"."TEST"."Long" ASC NULLS LAST; |
| 22 |
|
| 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), |
| 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) |
| 44 |
ELSE (0) |
| 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; |
| 51 |
|
| 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; |
| 54 |
|
| 55 |
-- Constant Column Primary Key
|
| 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; |
| 64 |
|
| 65 |
-- CLOB
|
| 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; |