Menu
▾
▴
[Tora-cvs] SF.net SVN: tora:[3531] branches/tora-trotl/sandbox
|
From: <ibr...@us...> - 2010-05-21 08:56:48
|
Revision: 3531
http://tora.svn.sourceforge.net/tora/?rev=3531&view=rev
Author: ibre5041
Date: 2010-05-21 08:56:41 +0000 (Fri, 21 May 2010)
Log Message:
-----------
test queries
Added Paths:
-----------
branches/tora-trotl/sandbox/SELECT_SIMPLE.u
branches/tora-trotl/sandbox/input1.sql.u
branches/tora-trotl/sandbox/input10.sql.u
branches/tora-trotl/sandbox/input11.sql.u
branches/tora-trotl/sandbox/input2.sql.u
branches/tora-trotl/sandbox/input20.sql.u
branches/tora-trotl/sandbox/input3.sql.u
branches/tora-trotl/sandbox/input4.sql.u
branches/tora-trotl/sandbox/input5.sql.u
branches/tora-trotl/sandbox/input6.sql.u
branches/tora-trotl/sandbox/input7.sql.u
branches/tora-trotl/sandbox/input8.sql.u
branches/tora-trotl/sandbox/input9.sql.u
branches/tora-trotl/sandbox/t1.sql
Added: branches/tora-trotl/sandbox/SELECT_SIMPLE.u
===================================================================
--- branches/tora-trotl/sandbox/SELECT_SIMPLE.u (rev 0)
+++ branches/tora-trotl/sandbox/SELECT_SIMPLE.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -0,0 +1,6 @@
+SELECT_SIMPLEParser.c : SELECT_SIMPLE.g
+./SELECT_SIMPLE.tokens : SELECT_SIMPLE.g
+SELECT_SIMPLEParser.h : SELECT_SIMPLE.g
+SELECT_SIMPLELexer.c : SELECT_SIMPLE.g
+SELECT_SIMPLELexer.h : SELECT_SIMPLE.g
+ANTLRGENLIST := SELECT_SIMPLEParser.c ./SELECT_SIMPLE.tokens SELECT_SIMPLEParser.h SELECT_SIMPLELexer.c SELECT_SIMPLELexer.h
Added: branches/tora-trotl/sandbox/input1.sql.u
===================================================================
--- branches/tora-trotl/sandbox/input1.sql.u (rev 0)
+++ branches/tora-trotl/sandbox/input1.sql.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -0,0 +1,2 @@
+SELECT EMPLOYEE_ID FROM (SELECT * FROM EMPLOYEES)
+ FOR UPDATE OF EMPLOYEE_ID;
Added: branches/tora-trotl/sandbox/input10.sql.u
===================================================================
--- branches/tora-trotl/sandbox/input10.sql.u (rev 0)
+++ branches/tora-trotl/sandbox/input10.sql.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -0,0 +1,19 @@
+WITH
+ DUP_HIREDATE AS --(EID, EMP_LAST, MGR_ID, REPORTLEVEL, HIRE_DATE, JOB_ID) AS
+ (
+ SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, 0 REPORTLEVEL, HIRE_DATE, 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.HIRE_DATE, E.JOB_ID
+ FROM DUP_HIREDATE R, EMPLOYEES E
+ WHERE R.EID = E.MANAGER_ID
+ )
+-- SEARCH DEPTH FIRST BY HIRE_DATE SET ORDER1
+-- CYCLE HIRE_DATE SET IS_CYCLE TO 'Y' DEFAULT 'N'
+SELECT LPAD(' ',2*REPORTLEVEL)||EMP_LAST EMP_NAME, EID, MGR_ID,
+ HIRE_DATE, JOB_ID, IS_CYCLE
+FROM DUP_HIREDATE
+ORDER BY ORDER1;
+
Added: branches/tora-trotl/sandbox/input11.sql.u
===================================================================
--- branches/tora-trotl/sandbox/input11.sql.u (rev 0)
+++ branches/tora-trotl/sandbox/input11.sql.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -0,0 +1,18 @@
+WITH
+ EMP_COUNT AS -- (EID, EMP_LAST, MGR_ID, MGRLEVEL, SALARY, CNT_EMPLOYEES) AS
+ (
+ SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, 0 MGRLEVEL, SALARY, 0 CNT_EMPLOYEES
+ FROM EMPLOYEES
+-- UNION ALL
+-- SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.MANAGER_ID,
+-- R.MGRLEVEL+1 MGRLEVEL, E.SALARY, 1 CNT_EMPLOYEES
+-- FROM EMP_COUNT R, EMPLOYEES E
+-- WHERE E.EMPLOYEE_ID = R.MGR_ID
+ )
+-- SEARCH DEPTH FIRST BY EMP_LAST SET ORDER1
+SELECT EMP_LAST, EID, MGR_ID, SALARY, SUM(CNT_EMPLOYEES), MAX(MGRLEVEL) MGRLEVEL
+FROM EMP_COUNT
+GROUP BY EMP_LAST, EID, MGR_ID, SALARY
+HAVING MAX(MGRLEVEL) > 0
+ORDER BY MGR_ID NULLS FIRST, EMP_LAST;
+
Added: branches/tora-trotl/sandbox/input2.sql.u
===================================================================
--- branches/tora-trotl/sandbox/input2.sql.u (rev 0)
+++ branches/tora-trotl/sandbox/input2.sql.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -0,0 +1,2 @@
+SELECT EMPLOYEE_ID FROM (SELECT EMPLOYEE_ID+1 AS EMPLOYEE_ID FROM EMPLOYEES)
+ FOR UPDATE;
Added: branches/tora-trotl/sandbox/input20.sql.u
===================================================================
--- branches/tora-trotl/sandbox/input20.sql.u (rev 0)
+++ branches/tora-trotl/sandbox/input20.sql.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -0,0 +1,8 @@
+ 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
+
Added: branches/tora-trotl/sandbox/input3.sql.u
===================================================================
--- branches/tora-trotl/sandbox/input3.sql.u (rev 0)
+++ branches/tora-trotl/sandbox/input3.sql.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -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/input4.sql.u
===================================================================
--- branches/tora-trotl/sandbox/input4.sql.u (rev 0)
+++ branches/tora-trotl/sandbox/input4.sql.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -0,0 +1,5 @@
+SELECT EMPLOYEE_ID FROM (SELECT EMPLOYEE_ID+1 AS EMPLOYEE_ID FROM EMPLOYEES)
+ FOR UPDATE OF EMPLOYEE_ID;
+
+
+
Added: branches/tora-trotl/sandbox/input5.sql.u
===================================================================
--- branches/tora-trotl/sandbox/input5.sql.u (rev 0)
+++ branches/tora-trotl/sandbox/input5.sql.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -0,0 +1,4 @@
+SELECT EMPLOYEE_ID FROM (SELECT EMPLOYEE_ID+1 AS EMPLOYEE_ID FROM EMPLOYEES)
+ FOR UPDATE OF EMPLOYEE_ID;
+
+
Added: branches/tora-trotl/sandbox/input6.sql.u
===================================================================
--- branches/tora-trotl/sandbox/input6.sql.u (rev 0)
+++ branches/tora-trotl/sandbox/input6.sql.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -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/input7.sql.u
===================================================================
--- branches/tora-trotl/sandbox/input7.sql.u (rev 0)
+++ branches/tora-trotl/sandbox/input7.sql.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -0,0 +1,16 @@
+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/input8.sql.u
===================================================================
--- branches/tora-trotl/sandbox/input8.sql.u (rev 0)
+++ branches/tora-trotl/sandbox/input8.sql.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -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/input9.sql.u
===================================================================
--- branches/tora-trotl/sandbox/input9.sql.u (rev 0)
+++ branches/tora-trotl/sandbox/input9.sql.u 2010-05-21 08:56:41 UTC (rev 3531)
@@ -0,0 +1,17 @@
+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/t1.sql
===================================================================
--- branches/tora-trotl/sandbox/t1.sql (rev 0)
+++ branches/tora-trotl/sandbox/t1.sql 2010-05-21 08:56:41 UTC (rev 3531)
@@ -0,0 +1,8 @@
+ 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, 1 REPORTLEVEL
+ FROM EMPLOYEES E
+ WHERE E.MANAGER_ID IS NOT NULL
+/
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|