Menu
▾
▴
[Tora-cvs] SF.net SVN: tora:[3533] branches/tora-trotl/sandbox
|
From: <ibr...@us...> - 2010-05-21 12:17:39
|
Revision: 3533
http://tora.svn.sourceforge.net/tora/?rev=3533&view=rev
Author: ibre5041
Date: 2010-05-21 12:17:33 +0000 (Fri, 21 May 2010)
Log Message:
-----------
some test queries added
Added Paths:
-----------
branches/tora-trotl/sandbox/tests/
branches/tora-trotl/sandbox/tests/analytic_query01.sql
branches/tora-trotl/sandbox/tests/case_when01.sql
branches/tora-trotl/sandbox/tests/connect_by01.sql
branches/tora-trotl/sandbox/tests/model_clause01.sql
branches/tora-trotl/sandbox/tests/query_factoring01.sql
branches/tora-trotl/sandbox/tests/query_factoring02.sql
branches/tora-trotl/sandbox/tests/query_factoring03.sql
branches/tora-trotl/sandbox/tests/query_factoring04.sql
branches/tora-trotl/sandbox/tests/query_factoring05.sql
branches/tora-trotl/sandbox/tests/simple01.sql
branches/tora-trotl/sandbox/tests/simple02.sql
branches/tora-trotl/sandbox/tests/simple03.sql
branches/tora-trotl/sandbox/tests/union01.sql
Added: branches/tora-trotl/sandbox/tests/analytic_query01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/analytic_query01.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/analytic_query01.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,31 @@
+WITH O(OBJ,LINK) AS
+(
+SELECT 'A', 'B' FROM DUAL UNION ALL
+SELECT 'A', 'C' FROM DUAL UNION ALL
+SELECT 'C', 'D' FROM DUAL UNION ALL
+SELECT 'D', 'C' FROM DUAL UNION ALL
+SELECT 'D', 'E' FROM DUAL UNION ALL
+SELECT 'E', 'E' FROM DUAL),
+T(ROOT,LEV,OBJ,LINK,PATH) AS (
+SELECT OBJ,1,OBJ,LINK,CAST(OBJ||'->'||LINK
+AS VARCHAR2(4000))
+FROM O
+WHERE OBJ='A' -- START WITH
+UNION ALL
+SELECT
+ T.ROOT,T.LEV+1,O.OBJ,O.LINK,
+ T.PATH||', '||O.OBJ||
+ '->'
+ ||O.LINK
+FROM T, O
+WHERE T.LINK=O.OBJ
+)
+SEARCH DEPTH FIRST BY OBJ SET ORD
+CYCLE OBJ SET CYCLE TO 1 DEFAULT 0
+SELECT ROOT,LEV,OBJ,LINK,PATH,CYCLE,
+ CASE
+ WHEN (LEV - LEAD(LEV) OVER (ORDER BY ORD)) < 0
+ THEN 0
+ ELSE 1
+ END IS_LEAF
+ FROM T
Added: branches/tora-trotl/sandbox/tests/case_when01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/case_when01.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/case_when01.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,8 @@
+SELECT
+ROOT,LEV,OBJ,LINK,PATH,CYCLE,
+ CASE
+ WHEN A=B -- (LEV - LEAD(LEV) OVER (ORDER BY ORD)) < 0
+ THEN 0
+ ELSE 1
+ END IS_LEAF
+FROM T
Added: branches/tora-trotl/sandbox/tests/connect_by01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/connect_by01.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/connect_by01.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,19 @@
+WITH O AS
+(
+ SELECT 'A' OBJ, 'B' LINK FROM DUAL UNION ALL
+ SELECT 'A', 'C' FROM DUAL UNION ALL
+ SELECT 'C', 'D' FROM DUAL UNION ALL
+ SELECT 'D', 'C' FROM DUAL UNION ALL
+ SELECT 'D', 'E' FROM DUAL UNION ALL
+ SELECT 'E', 'E' FROM DUAL
+)
+SELECT
+ CONNECT_BY_ROOT OBJ ROOT,
+ LEVEL,
+ OBJ,LINK,
+ SYS_CONNECT_BY_PATH(OBJ||'->'||LINK,','),
+ CONNECT_BY_ISCYCLE,
+ CONNECT_BY_ISLEAF
+FROM O
+CONNECT BY NOCYCLE OBJ=PRIOR LINK
+START WITH OBJ='A'
Added: branches/tora-trotl/sandbox/tests/model_clause01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/model_clause01.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/model_clause01.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,16 @@
+SELECT COUNTRY,PROD,YEAR,S
+FROM SALES_VIEW_REF
+MODEL
+PARTITION BY (COUNTRY)
+DIMENSION BY (PROD, YEAR)
+MEASURES (SALE S)
+IGNORE NAV
+-- cell_reference_options
+UNIQUE DIMENSION
+-- here starts model_rules_clause
+RULES UPSERT SEQUENTIAL ORDER
+(
+S[PROD='Mouse Pad', YEAR=2001] = S['Mouse Pad', 1999] + S['Mouse Pad', 2000],
+S['Standard Mouse', 2002] = S['Standard Mouse', 2001]
+)
+ORDER BY COUNTRY, PROD, YEAR
Added: branches/tora-trotl/sandbox/tests/query_factoring01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/query_factoring01.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/query_factoring01.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,16 @@
+WITH
+ REPORTS_TO_101 (EID, EMP_LAST, MGR_ID, REPORTLEVEL) AS
+ (
+ SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, 0 REPORTLEVEL
+ FROM EMPLOYEES
+ WHERE EMPLOYEE_ID = 101
+ UNION ALL
+ SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.MANAGER_ID, REPORTLEVEL+1
+ FROM REPORTS_TO_101 R, EMPLOYEES E
+ WHERE R.EID = E.MANAGER_ID
+ )
+SELECT EID, EMP_LAST, MGR_ID, REPORTLEVEL
+FROM REPORTS_TO_101
+ORDER BY REPORTLEVEL, EID;
+
+
Added: branches/tora-trotl/sandbox/tests/query_factoring02.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/query_factoring02.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/query_factoring02.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,17 @@
+WITH
+ REPORTS_TO_101 (EID, EMP_LAST, MGR_ID, REPORTLEVEL, MGR_LIST)
+ AS
+ (
+ SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, 0 REPORTLEVEL
+ , CAST(MANAGER_ID AS VARCHAR2(2000))
+ FROM EMPLOYEES
+ WHERE EMPLOYEE_ID = 101
+ UNION ALL
+ SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.MANAGER_ID, REPORTLEVEL+1
+ , CAST(MGR_LIST || ',' || MANAGER_ID AS VARCHAR2(2000))
+ FROM REPORTS_TO_101 R, EMPLOYEES E
+ WHERE R.EID = E.MANAGER_ID
+ )
+SELECT EID, EMP_LAST, MGR_ID, REPORTLEVEL, MGR_LIST
+FROM REPORTS_TO_101
+ORDER BY REPORTLEVEL, EID;
Added: branches/tora-trotl/sandbox/tests/query_factoring03.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/query_factoring03.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/query_factoring03.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,15 @@
+WITH
+ REPORTS_TO_101 (EID, EMP_LAST, MGR_ID, REPORTLEVEL) AS
+ (
+ SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, 0 REPORTLEVEL
+ FROM EMPLOYEES
+ WHERE EMPLOYEE_ID = 101
+ UNION ALL
+ SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.MANAGER_ID, REPORTLEVEL+1
+ FROM REPORTS_TO_101 R, EMPLOYEES E
+ WHERE R.EID = E.MANAGER_ID
+ )
+SELECT EID, EMP_LAST, MGR_ID, REPORTLEVEL
+FROM REPORTS_TO_101
+WHERE REPORTLEVEL <= 1
+ORDER BY REPORTLEVEL, EID;
Added: branches/tora-trotl/sandbox/tests/query_factoring04.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/query_factoring04.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/query_factoring04.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,16 @@
+WITH
+ ORG_CHART (EID, EMP_LAST, MGR_ID, REPORTLEVEL, SALARY, JOB_ID) AS
+ (
+ SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, 0 REPORTLEVEL, SALARY, JOB_ID
+ FROM EMPLOYEES
+ WHERE MANAGER_ID IS NULL
+ UNION ALL
+ SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.MANAGER_ID,
+ R.REPORTLEVEL+1 REPORTLEVEL, E.SALARY, E.JOB_ID
+ FROM ORG_CHART R, EMPLOYEES E
+ WHERE R.EID = E.MANAGER_ID
+ )
+ SEARCH DEPTH FIRST BY EMP_LAST SET ORDER1
+SELECT LPAD(' ',2*REPORTLEVEL)||EMP_LAST EMP_NAME, EID, MGR_ID, SALARY, JOB_ID
+FROM ORG_CHART
+ORDER BY ORDER1
Added: branches/tora-trotl/sandbox/tests/query_factoring05.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/query_factoring05.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/query_factoring05.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,28 @@
+WITH
+X1 AS (SELECT MAX(Y1) FROM KLM1),
+X2 AS (SELECT MAX(Y2) FROM KLM2),
+X3 AS (SELECT MAX(Y3) FROM KLM3),
+X4 AS (SELECT MAX(Y4) FROM KLM4)
+SELECT
+ DISTINCT
+ -1,
+ +1,
+ A + B * (A * D) AS,
+ T1.REGION_NAME,
+ T2.DIVISION_NAME,
+ T1.REGION_NAME AS A,
+ T2.DIVISION_NAME AS,
+ *,
+ A.*,
+ SUM(T3.AMOUNT),
+ SUM(COUNT(1)) + COUNT(*)
+ , SUM(1) + (SELECT COUNT(1) FROM DDD) A
+FROM DUAL, FFF
+WHERE A IS NULL
+OR B IS NOT NULL
+AND ( A LIKE 'D')
+AND 1 = 0
+AND A.B.C IS A SET
+UNION
+SELECT A FROM DUAL
+
Added: branches/tora-trotl/sandbox/tests/simple01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/simple01.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/simple01.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,2 @@
+SELECT EMPLOYEE_ID FROM (SELECT * FROM EMPLOYEES)
+ FOR UPDATE OF EMPLOYEE_ID;
Added: branches/tora-trotl/sandbox/tests/simple02.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/simple02.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/simple02.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,2 @@
+SELECT EMPLOYEE_ID FROM (SELECT EMPLOYEE_ID+1 AS EMPLOYEE_ID FROM EMPLOYEES)
+ FOR UPDATE;
Added: branches/tora-trotl/sandbox/tests/simple03.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/simple03.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/simple03.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,3 @@
+SELECT EMPLOYEE_ID FROM (SELECT EMPLOYEE_ID+1 AS EMPLOYEE_ID FROM EMPLOYEES)
+ FOR UPDATE OF EMPLOYEE_ID;
+
Added: branches/tora-trotl/sandbox/tests/union01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/union01.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/union01.sql 2010-05-21 12:17:33 UTC (rev 3533)
@@ -0,0 +1,6 @@
+SELECT 'A' OBJ, 'B' LINK FROM DUAL UNION ALL
+SELECT 'A', 'C' FROM DUAL UNION ALL
+SELECT 'C', 'D' FROM DUAL UNION ALL
+SELECT 'D', 'C' FROM DUAL UNION ALL
+SELECT 'D', 'E' FROM DUAL UNION ALL
+SELECT 'E', 'E' FROM DUAL)
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|