Menu
▾
▴
[Tora-cvs] SF.net SVN: tora:[3958] branches/tora-trotl/sandbox/tests
|
From: <ibr...@us...> - 2011-05-13 14:35:50
|
Revision: 3958
http://tora.svn.sourceforge.net/tora/?rev=3958&view=rev
Author: ibre5041
Date: 2011-05-13 14:35:40 +0000 (Fri, 13 May 2011)
Log Message:
-----------
new files in SQL test suite
Modified Paths:
--------------
branches/tora-trotl/sandbox/tests/case_when01.sql
branches/tora-trotl/sandbox/tests/cast_multiset02.sql
branches/tora-trotl/sandbox/tests/columns01.sql
branches/tora-trotl/sandbox/tests/complex02.sql
branches/tora-trotl/sandbox/tests/condition01.sql
branches/tora-trotl/sandbox/tests/condition02.sql
branches/tora-trotl/sandbox/tests/merge01.sql
branches/tora-trotl/sandbox/tests/simple01.sql
branches/tora-trotl/sandbox/tests/union01.sql
Added Paths:
-----------
branches/tora-trotl/sandbox/tests/analytic_query05.sql
branches/tora-trotl/sandbox/tests/bindvar01.sql
branches/tora-trotl/sandbox/tests/bindvar02.sql
branches/tora-trotl/sandbox/tests/bindvar03.sql
branches/tora-trotl/sandbox/tests/case_when02.sql
branches/tora-trotl/sandbox/tests/case_when03.sql
branches/tora-trotl/sandbox/tests/cast_multiset05.sql
branches/tora-trotl/sandbox/tests/condition04.sql
branches/tora-trotl/sandbox/tests/condition05.sql
branches/tora-trotl/sandbox/tests/condition06.sql
branches/tora-trotl/sandbox/tests/condition07.sql
branches/tora-trotl/sandbox/tests/condition08.sql
branches/tora-trotl/sandbox/tests/condition09.sql
branches/tora-trotl/sandbox/tests/condition10.sql
branches/tora-trotl/sandbox/tests/condition11.sql
branches/tora-trotl/sandbox/tests/condition12.sql
branches/tora-trotl/sandbox/tests/datetime02.sql
branches/tora-trotl/sandbox/tests/datetime03.sql
branches/tora-trotl/sandbox/tests/datetime04.sql
branches/tora-trotl/sandbox/tests/for_update08.sql
branches/tora-trotl/sandbox/tests/function02.sql
branches/tora-trotl/sandbox/tests/function03.sql
branches/tora-trotl/sandbox/tests/function04.sql
branches/tora-trotl/sandbox/tests/groupby01.sql
branches/tora-trotl/sandbox/tests/groupby02.sql
branches/tora-trotl/sandbox/tests/groupby03.sql
branches/tora-trotl/sandbox/tests/groupby04.sql
branches/tora-trotl/sandbox/tests/groupby05.sql
branches/tora-trotl/sandbox/tests/interval02.sql
branches/tora-trotl/sandbox/tests/join17.sql
branches/tora-trotl/sandbox/tests/merge02.sql
branches/tora-trotl/sandbox/tests/object_access.sql
branches/tora-trotl/sandbox/tests/parens01.sql
branches/tora-trotl/sandbox/tests/parens02.sql
branches/tora-trotl/sandbox/tests/query_factoring07.sql
branches/tora-trotl/sandbox/tests/union03.sql
branches/tora-trotl/sandbox/tests/union04.sql
branches/tora-trotl/sandbox/tests/union05.sql
branches/tora-trotl/sandbox/tests/union06.sql
Added: branches/tora-trotl/sandbox/tests/analytic_query05.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/analytic_query05.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/analytic_query05.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,17 @@
+ SELECT METRIC_ID ,BSLN_GUID ,TIMEGROUP ,OBS_VALUE AS OBS_VALUE ,
+ CUME_DIST () OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ORDER BY OBS_VALUE ) AS CUME_DIST ,
+ COUNT(1) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ) AS N ,
+ ROW_NUMBER () OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ORDER BY OBS_VALUE) AS RRANK ,
+ PERCENTILE_DISC(:B7 ) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS MID_TAIL_VALUE ,
+ MAX(OBS_VALUE) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ) AS MAX_VAL ,
+ MIN(OBS_VALUE) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ) AS MIN_VAL ,
+ AVG(OBS_VALUE) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ) AS AVG_VAL ,
+ STDDEV(OBS_VALUE) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ) AS SDEV_VAL ,
+ PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS PCTILE_25 ,
+ PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS PCTILE_50 ,
+ PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS PCTILE_75 ,
+ PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS PCTILE_90 ,
+ PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS PCTILE_95 ,
+ PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS PCTILE_99
+ FROM TIMEGROUPED_RAWDATA D
+;
\ No newline at end of file
Added: branches/tora-trotl/sandbox/tests/bindvar01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/bindvar01.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/bindvar01.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,14 @@
+INSERT INTO P
+(
+A1,
+B2,
+C3,
+D4,
+E5,
+F6,
+G7,
+H8
+)
+VALUES
+( :B1, :B2, :B3, :B4, :5, :6, :7, :8)
+;
Added: branches/tora-trotl/sandbox/tests/bindvar02.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/bindvar02.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/bindvar02.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,6 @@
+SELECT *
+FROM A
+WHERE A=:3
+AND B= : 4
+AND C= :5AND :A = :B
+;
Added: branches/tora-trotl/sandbox/tests/bindvar03.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/bindvar03.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/bindvar03.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,19 @@
+select count(*), max(scn)
+from
+(
+ select sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags
+ from sumpartlog$ sp, sumdep$ sd
+ where sd.sumobj# = :1 and sd.p_obj# = sp.bo#
+ group by sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags
+ minus
+ select sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags
+ from sumpartlog$ sp
+ where sp.bo# not in
+ (
+ select sk.DETAILOBJ# from sumkey$ sk where sk.sumobj# = :1 and sk.DETAILCOLFUNCTION in (2,3)
+ )
+ and bitand(sp.flags, 2) != 2 and sp.PMOPTYPE in (2,3,5,7)
+ group by sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags
+)
+where scn > : 2
+;
\ No newline at end of file
Modified: branches/tora-trotl/sandbox/tests/case_when01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/case_when01.sql 2011-05-05 23:39:31 UTC (rev 3957)
+++ branches/tora-trotl/sandbox/tests/case_when01.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -1,8 +1,7 @@
SELECT
ROOT,LEV,OBJ,LINK,PATH,CYCLE,
CASE
- WHEN A=B -- (LEV - LEAD(LEV) OVER (ORDER BY ORD)) < 0
- THEN 0
+ 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_when02.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/case_when02.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/case_when02.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,32 @@
+SELECT
+ STALENESS, OSIZE, OBJ#, TYPE#,
+ CASE
+ WHEN STALENESS > .5 THEN 128
+ WHEN STALENESS > .1 THEN 256
+ ELSE 0
+ END + AFLAGS AFLAGS,
+ STATUS,
+ SID,
+ SERIAL#,
+ PART#,
+ BO#
+ ,
+ CASE
+ WHEN IS_FULL_EVENTS_HISTORY = 1 THEN SRC.BOR_LAST_STATUS_TIME
+ ELSE
+ CASE GREATEST (NVL (WP.BOR_LAST_STAT_TIME, DATE '1900-01-01'), NVL (SRC.BOR_LAST_STATUS_TIME, DATE '1900-01-01'))
+ WHEN DATE '1900-01-01' THEN NULL
+ WHEN WP.BOR_LAST_STAT_TIME THEN WP.BOR_LAST_STAT_TIME
+ WHEN SRC.BOR_LAST_STATUS_TIME THEN SRC.BOR_LAST_STATUS_TIME
+ ELSE NULL
+ END
+ END
+ ,
+ CASE GREATEST (NVL (WP.BOR_LAST_STAT_TIME, DATE '1900-01-01'), NVL (SRC.BOR_LAST_STATUS_TIME, DATE '1900-01-01'))
+ WHEN DATE '1900-01-01' THEN NULL
+ WHEN WP.BOR_LAST_STAT_TIME THEN WP.BOR_LAST_STAT_TIME
+ WHEN SRC.BOR_LAST_STATUS_TIME THEN SRC.BOR_LAST_STATUS_TIME
+ ELSE NULL
+ END
+FROM X
+;
Added: branches/tora-trotl/sandbox/tests/case_when03.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/case_when03.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/case_when03.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,9 @@
+SELECT
+CASE (STATUS)
+WHEN 'N' THEN 1
+WHEN 'B' THEN 2
+WHEN 'A' THEN 3
+END AS STATE
+FROM VALUE
+WHERE KID=:B2 AND RID=:B1
+;
Modified: branches/tora-trotl/sandbox/tests/cast_multiset02.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/cast_multiset02.sql 2011-05-05 23:39:31 UTC (rev 3957)
+++ branches/tora-trotl/sandbox/tests/cast_multiset02.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -1,7 +1,6 @@
SELECT title
FROM
- dual d,
- TABLE(SELECT courses FROM department WHERE name = 'History') c
+ TABLE(SELECT courses FROM department WHERE name = 'History')
WHERE name LIKE '%Etruscan%'
;
Added: branches/tora-trotl/sandbox/tests/cast_multiset05.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/cast_multiset05.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/cast_multiset05.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,7 @@
+SELECT *
+FROM
+ TABLE
+ (
+ function_name()
+ )
+;
\ No newline at end of file
Modified: branches/tora-trotl/sandbox/tests/columns01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/columns01.sql 2011-05-05 23:39:31 UTC (rev 3957)
+++ branches/tora-trotl/sandbox/tests/columns01.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -1,6 +1,7 @@
SELECT A, B,
A D,
DDD AS DDD,
-DDD AS "DFDF"
+DDD AS "DFDF",
+X AS
FROM dual
;
Modified: branches/tora-trotl/sandbox/tests/complex02.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/complex02.sql 2011-05-05 23:39:31 UTC (rev 3957)
+++ branches/tora-trotl/sandbox/tests/complex02.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -1,415 +1,323 @@
select * from (
-
-with Inventory as
-
+with
+Inventory as
(SELECT
-
- I.ORGANIZATION_CODE AS CustomerName,
-
- D.SHIPNODE_KEY AS SHIPNODE_KEY,
-
- I.ITEM_ID AS item_id,
-
- IT.ITEM_KEY as item_key,
-
- sum(atp) as ATP
-
- FROM
-
- selprod.YFS_INVENTORY_ITEM I,selprod.YFS_ITEM IT,
-
- (SELECT
-
- inventory_item_key ,SHIPNODE_KEY,
-
- NVL(SUM(CASE WHEN DEMAND_TYPE = 'ONHAND' THEN QUANTITY ELSE 0 END),0) -
-
- NVL(SUM(CASE WHEN DEMAND_TYPE = 'ALLOCATED' THEN QUANTITY ELSE 0 END),0) -
-
- NVL(SUM(CASE WHEN DEMAND_TYPE = 'SCHEDULED' THEN QUANTITY ELSE 0 END),0)
-
- AS "ATP"
-
- FROM selprod.YFS_INVENTORY_DEMAND d
-
- WHERE
-
- --owner_key='DELL' and
-
- QUANTITY<>0
-
- and (DEMAND_TYPE='ALLOCATED' OR DEMAND_TYPE='BACKORDER' OR DEMAND_TYPE='OPEN_ORDER' OR DEMAND_TYPE='SCHEDULED')
-
- AND SHIPNODE_KEY IS NOT NULL AND TRIM(SHIPNODE_KEY) IS NOT NULL
-
- group by INVENTORY_ITEM_KEY,SHIPNODE_KEY
-
- UNION ALL
-
- SELECT
-
- INVENTORY_ITEM_KEY,SHIPNODE_KEY,
-
- NVL(SUM(CASE WHEN SUPPLY_TYPE = 'ONHAND' THEN QUANTITY ELSE 0 END),0) -
-
- NVL(SUM(CASE WHEN SUPPLY_TYPE = 'ALLOCATED' THEN QUANTITY ELSE 0 END),0) -
-
- NVL(SUM(CASE WHEN SUPPLY_TYPE = 'SCHEDULED' THEN QUANTITY ELSE 0 END),0) -
-
- NVL(SUM(CASE WHEN SUPPLY_TYPE = 'ONHAND' AND SHIP_BY_DATE < SYSDATE THEN QUANTITY ELSE 0 END),0)
-
- AS "ATP"
-
- FROM selprod.YFS_INVENTORY_SUPPLY
-
- WHERE
-
- --owner_key='DELL' and
-
- QUANTITY<>0
-
- and (SUPPLY_TYPE='HELD' OR SUPPLY_TYPE='INTRANSIT' OR SUPPLY_TYPE='ONHAND' OR SUPPLY_TYPE='PO_RELEASED')
-
- AND SHIPNODE_KEY IS NOT NULL AND TRIM(SHIPNODE_KEY) IS NOT NULL
-
- group by INVENTORY_ITEM_KEY,SHIPNODE_KEY
-
- ) D
-
- WHERE
-
- I.organization_code='DELL' and
-
- I.ITEM_ID=IT.ITEM_ID and
-
- I.INVENTORY_ITEM_KEY=D.INVENTORY_ITEM_KEY and
-
- i.product_class='GOOD'
-
- GROUP BY I.ORGANIZATION_CODE,D.SHIPNODE_KEY,I.ITEM_ID,IT.ITEM_KEY
-
- having sum(atp)>0
-
+ I.ORGANIZATION_CODE AS CustomerName,
+ D.SHIPNODE_KEY AS SHIPNODE_KEY,
+ I.ITEM_ID AS item_id,
+ IT.ITEM_KEY as item_key,
+ sum(atp) as ATP
+ FROM
+ selprod.YFS_INVENTORY_ITEM I,selprod.YFS_ITEM IT,
+ (
+ SELECT
+ INVENTORY_ITEM_KEY, SHIPNODE_KEY,
+ NVL(SUM(CASE WHEN DEMAND_TYPE = 'ONHAND' THEN QUANTITY ELSE 0 END),0) -
+ NVL(SUM(CASE WHEN DEMAND_TYPE = 'ALLOCATED' THEN QUANTITY ELSE 0 END),0) -
+ NVL(SUM(CASE WHEN DEMAND_TYPE = 'SCHEDULED' THEN QUANTITY ELSE 0 END),0) AS "ATP"
+ FROM selprod.YFS_INVENTORY_DEMAND d
+ WHERE
+ --owner_key='DELL' and
+ QUANTITY<>0
+ AND (DEMAND_TYPE='ALLOCATED' OR DEMAND_TYPE='BACKORDER' OR DEMAND_TYPE='OPEN_ORDER' OR DEMAND_TYPE='SCHEDULED')
+ AND SHIPNODE_KEY IS NOT NULL AND TRIM(SHIPNODE_KEY) IS NOT NULL
+ GROUP BY INVENTORY_ITEM_KEY,SHIPNODE_KEY
+ UNION ALL
+ SELECT
+ INVENTORY_ITEM_KEY, SHIPNODE_KEY,
+ NVL(SUM(CASE WHEN SUPPLY_TYPE = 'ONHAND' THEN QUANTITY ELSE 0 END),0) -
+ NVL(SUM(CASE WHEN SUPPLY_TYPE = 'ALLOCATED' THEN QUANTITY ELSE 0 END),0) -
+ NVL(SUM(CASE WHEN SUPPLY_TYPE = 'SCHEDULED' THEN QUANTITY ELSE 0 END),0) -
+ NVL(SUM(CASE WHEN SUPPLY_TYPE = 'ONHAND' AND SHIP_BY_DATE < SYSDATE THEN QUANTITY ELSE 0 END),0) AS "ATP"
+ FROM selprod.YFS_INVENTORY_SUPPLY
+ WHERE
+ --owner_key='DELL' and
+ QUANTITY<>0
+ AND (SUPPLY_TYPE='HELD' OR SUPPLY_TYPE='INTRANSIT' OR SUPPLY_TYPE='ONHAND' OR SUPPLY_TYPE='PO_RELEASED')
+ AND SHIPNODE_KEY IS NOT NULL AND TRIM(SHIPNODE_KEY) IS NOT NULL
+ GROUP BY INVENTORY_ITEM_KEY,SHIPNODE_KEY
+ ) D
+ WHERE
+ I.organization_code='DELL'
+ AND I.ITEM_ID=IT.ITEM_ID
+ AND I.INVENTORY_ITEM_KEY=D.INVENTORY_ITEM_KEY
+ AND i.product_class='GOOD'
+ GROUP BY I.ORGANIZATION_CODE,D.SHIPNODE_KEY,I.ITEM_ID,IT.ITEM_KEY
+ HAVING SUM(atp)>0
),
-
FlatInventory as
-
-(select parent_key,CustomerName,SHIPNODE_KEY,
-
- max(decode(seq,1,trim(item_id),null)) AltItemID1,max(decode(seq,1,ATP,null)) ATP1,
-
- max(decode(seq,2,trim(item_id),null)) AltItemID2,max(decode(seq,2,ATP,null)) ATP2,
-
- max(decode(seq,3,trim(item_id),null)) AltItemID3,max(decode(seq,3,ATP,null)) ATP3,
-
- max(decode(seq,4,trim(item_id),null)) AltItemID4,max(decode(seq,4,ATP,null)) ATP4,
-
- max(decode(seq,5,trim(item_id),null)) AltItemID5,max(decode(seq,5,ATP,null)) ATP5,
-
- max(decode(seq,6,trim(item_id),null)) AltItemID6,max(decode(seq,6,ATP,null)) ATP6,
-
- max(decode(seq,7,trim(item_id),null)) AltItemID7,max(decode(seq,7,ATP,null)) ATP7,
-
- max(decode(seq,8,trim(item_id),null)) AltItemID8,max(decode(seq,8,ATP,null)) ATP8,
-
- max(decode(seq,9,trim(item_id),null)) AltItemID9,max(decode(seq,9,ATP,null)) ATP9,
-
- max(decode(seq,10,trim(item_id),null)) AltItemID10,max(decode(seq,10,ATP,null)) ATP10,
-
- max(decode(seq,11,trim(item_id),null)) AltItemID11,max(decode(seq,11,ATP,null)) ATP11,
-
- max(decode(seq,12,trim(item_id),null)) AltItemID12,max(decode(seq,12,ATP,null)) ATP12,
-
- max(decode(seq,13,trim(item_id),null)) AltItemID13,max(decode(seq,13,ATP,null)) ATP13,
-
- max(decode(seq,14,trim(item_id),null)) AltItemID14,max(decode(seq,14,ATP,null)) ATP14,
-
- max(decode(seq,15,trim(item_id),null)) AltItemID15,max(decode(seq,15,ATP,null)) ATP15,
-
- max(decode(seq,16,trim(item_id),null)) AltItemID16,max(decode(seq,16,ATP,null)) ATP16,
-
- max(decode(seq,17,trim(item_id),null)) AltItemID17,max(decode(seq,17,ATP,null)) ATP17,
-
- max(decode(seq,18,trim(item_id),null)) AltItemID18,max(decode(seq,18,ATP,null)) ATP18,
-
- max(decode(seq,19,trim(item_id),null)) AltItemID19,max(decode(seq,19,ATP,null)) ATP19,
-
- max(decode(seq,20,trim(item_id),null)) AltItemID20,max(decode(seq,20,ATP,null)) ATP20,
-
- max(decode(seq,21,trim(item_id),null)) AltItemID21,max(decode(seq,21,ATP,null)) ATP21,
-
- max(decode(seq,22,trim(item_id),null)) AltItemID22,max(decode(seq,22,ATP,null)) ATP22,
-
- max(decode(seq,23,trim(item_id),null)) AltItemID23,max(decode(seq,23,ATP,null)) ATP23,
-
- max(decode(seq,24,trim(item_id),null)) AltItemID24,max(decode(seq,24,ATP,null)) ATP24,
-
- max(decode(seq,25,trim(item_id),null)) AltItemID25,max(decode(seq,25,ATP,null)) ATP25,
-
- max(decode(seq,26,trim(item_id),null)) AltItemID26,max(decode(seq,26,ATP,null)) ATP26,
-
- max(decode(seq,27,trim(item_id),null)) AltItemID27,max(decode(seq,27,ATP,null)) ATP27,
-
- max(decode(seq,28,trim(item_id),null)) AltItemID28,max(decode(seq,28,ATP,null)) ATP28,
-
- max(decode(seq,29,trim(item_id),null)) AltItemID29,max(decode(seq,29,ATP,null)) ATP29,
-
- max(decode(seq,30,trim(item_id),null)) AltItemID30,max(decode(seq,30,ATP,null)) ATP30,
-
- max(decode(seq,31,trim(item_id),null)) AltItemID31,max(decode(seq,31,ATP,null)) ATP31,
-
- max(decode(seq,32,trim(item_id),null)) AltItemID32,max(decode(seq,32,ATP,null)) ATP32,
-
- max(decode(seq,33,trim(item_id),null)) AltItemID33,max(decode(seq,33,ATP,null)) ATP33,
-
- max(decode(seq,34,trim(item_id),null)) AltItemID34,max(decode(seq,34,ATP,null)) ATP34,
-
- max(decode(seq,35,trim(item_id),null)) AltItemID35,max(decode(seq,35,ATP,null)) ATP35,
-
- max(decode(seq,36,trim(item_id),null)) AltItemID36,max(decode(seq,36,ATP,null)) ATP36,
-
- max(decode(seq,37,trim(item_id),null)) AltItemID37,max(decode(seq,37,ATP,null)) ATP37,
-
- max(decode(seq,38,trim(item_id),null)) AltItemID38,max(decode(seq,38,ATP,null)) ATP38,
-
- max(decode(seq,39,trim(item_id),null)) AltItemID39,max(decode(seq,39,ATP,null)) ATP39,
-
- max(decode(seq,40,trim(item_id),null)) AltItemID40,max(decode(seq,40,ATP,null)) ATP40,
-
- max(decode(seq,41,trim(item_id),null)) AltItemID41,max(decode(seq,41,ATP,null)) ATP41,
-
- max(decode(seq,42,trim(item_id),null)) AltItemID42,max(decode(seq,42,ATP,null)) ATP42,
-
- max(decode(seq,43,trim(item_id),null)) AltItemID43,max(decode(seq,43,ATP,null)) ATP43,
-
- max(decode(seq,44,trim(item_id),null)) AltItemID44,max(decode(seq,44,ATP,null)) ATP44,
-
- max(decode(seq,45,trim(item_id),null)) AltItemID45,max(decode(seq,45,ATP,null)) ATP45,
-
- max(decode(seq,46,trim(item_id),null)) AltItemID46,max(decode(seq,46,ATP,null)) ATP46,
-
- max(decode(seq,47,trim(item_id),null)) AltItemID47,max(decode(seq,47,ATP,null)) ATP47,
-
- max(decode(seq,48,trim(item_id),null)) AltItemID48,max(decode(seq,48,ATP,null)) ATP48,
-
- max(decode(seq,49,trim(item_id),null)) AltItemID49,max(decode(seq,49,ATP,null)) ATP49,
-
- max(decode(seq,50,trim(item_id),null)) AltItemID50,max(decode(seq,50,ATP,null)) ATP50,
-
- max(decode(seq,51,trim(item_id),null)) AltItemID51,max(decode(seq,51,ATP,null)) ATP51,
-
- max(decode(seq,52,trim(item_id),null)) AltItemID52,max(decode(seq,52,ATP,null)) ATP52,
-
- max(decode(seq,53,trim(item_id),null)) AltItemID53,max(decode(seq,53,ATP,null)) ATP53,
-
- max(decode(seq,54,trim(item_id),null)) AltItemID54,max(decode(seq,54,ATP,null)) ATP54,
-
- max(decode(seq,55,trim(item_id),null)) AltItemID55,max(decode(seq,55,ATP,null)) ATP55,
-
- max(decode(seq,56,trim(item_id),null)) AltItemID56,max(decode(seq,56,ATP,null)) ATP56,
-
- max(decode(seq,57,trim(item_id),null)) AltItemID57,max(decode(seq,57,ATP,null)) ATP57,
-
- max(decode(seq,58,trim(item_id),null)) AltItemID58,max(decode(seq,58,ATP,null)) ATP58,
-
- max(decode(seq,59,trim(item_id),null)) AltItemID59,max(decode(seq,59,ATP,null)) ATP59,
-
- max(decode(seq,60,trim(item_id),null)) AltItemID60,max(decode(seq,60,ATP,null)) ATP60,
-
- max(decode(seq,61,trim(item_id),null)) AltItemID61,max(decode(seq,61,ATP,null)) ATP61,
-
- max(decode(seq,62,trim(item_id),null)) AltItemID62,max(decode(seq,62,ATP,null)) ATP62,
-
- max(decode(seq,63,trim(item_id),null)) AltItemID63,max(decode(seq,63,ATP,null)) ATP63,
-
- max(decode(seq,64,trim(item_id),null)) AltItemID64,max(decode(seq,64,ATP,null)) ATP64,
-
- max(decode(seq,65,trim(item_id),null)) AltItemID65,max(decode(seq,65,ATP,null)) ATP65,
-
- max(decode(seq,66,trim(item_id),null)) AltItemID66,max(decode(seq,66,ATP,null)) ATP66,
-
- max(decode(seq,67,trim(item_id),null)) AltItemID67,max(decode(seq,67,ATP,null)) ATP67,
-
- max(decode(seq,68,trim(item_id),null)) AltItemID68,max(decode(seq,68,ATP,null)) ATP68,
-
- max(decode(seq,69,trim(item_id),null)) AltItemID69,max(decode(seq,69,ATP,null)) ATP69,
-
- max(decode(seq,70,trim(item_id),null)) AltItemID70,max(decode(seq,70,ATP,null)) ATP70,
-
- max(decode(seq,71,trim(item_id),null)) AltItemID71,max(decode(seq,71,ATP,null)) ATP71,
-
- max(decode(seq,72,trim(item_id),null)) AltItemID72,max(decode(seq,72,ATP,null)) ATP72,
-
- max(decode(seq,73,trim(item_id),null)) AltItemID73,max(decode(seq,73,ATP,null)) ATP73,
-
- max(decode(seq,74,trim(item_id),null)) AltItemID74,max(decode(seq,74,ATP,null)) ATP74,
-
- max(decode(seq,75,trim(item_id),null)) AltItemID75,max(decode(seq,75,ATP,null)) ATP75,
-
- max(decode(seq,76,trim(item_id),null)) AltItemID76,max(decode(seq,76,ATP,null)) ATP76,
-
- max(decode(seq,77,trim(item_id),null)) AltItemID77,max(decode(seq,77,ATP,null)) ATP77,
-
- max(decode(seq,78,trim(item_id),null)) AltItemID78,max(decode(seq,78,ATP,null)) ATP78,
-
- max(decode(seq,79,trim(item_id),null)) AltItemID79,max(decode(seq,79,ATP,null)) ATP79,
-
- max(decode(seq,80,trim(item_id),null)) AltItemID80,max(decode(seq,80,ATP,null)) ATP80,
-
- max(decode(seq,81,trim(item_id),null)) AltItemID81,max(decode(seq,81,ATP,null)) ATP81,
-
- max(decode(seq,82,trim(item_id),null)) AltItemID82,max(decode(seq,82,ATP,null)) ATP82,
-
- max(decode(seq,83,trim(item_id),null)) AltItemID83,max(decode(seq,83,ATP,null)) ATP83,
-
- max(decode(seq,84,trim(item_id),null)) AltItemID84,max(decode(seq,84,ATP,null)) ATP84,
-
- max(decode(seq,85,trim(item_id),null)) AltItemID85,max(decode(seq,85,ATP,null)) ATP85,
-
- max(decode(seq,86,trim(item_id),null)) AltItemID86,max(decode(seq,86,ATP,null)) ATP86,
-
- max(decode(seq,87,trim(item_id),null)) AltItemID87,max(decode(seq,87,ATP,null)) ATP87,
-
- max(decode(seq,88,trim(item_id),null)) AltItemID88,max(decode(seq,88,ATP,null)) ATP88,
-
- max(decode(seq,89,trim(item_id),null)) AltItemID89,max(decode(seq,89,ATP,null)) ATP89,
-
- max(decode(seq,90,trim(item_id),null)) AltItemID90,max(decode(seq,90,ATP,null)) ATP90,
-
- max(decode(seq,91,trim(item_id),null)) AltItemID91,max(decode(seq,91,ATP,null)) ATP91,
-
- max(decode(seq,92,trim(item_id),null)) AltItemID92,max(decode(seq,92,ATP,null)) ATP92,
-
- max(decode(seq,93,trim(item_id),null)) AltItemID93,max(decode(seq,93,ATP,null)) ATP93,
-
- max(decode(seq,94,trim(item_id),null)) AltItemID94,max(decode(seq,94,ATP,null)) ATP94,
-
- max(decode(seq,95,trim(item_id),null)) AltItemID95,max(decode(seq,95,ATP,null)) ATP95,
-
- max(decode(seq,96,trim(item_id),null)) AltItemID96,max(decode(seq,96,ATP,null)) ATP96,
-
- max(decode(seq,97,trim(item_id),null)) AltItemID97,max(decode(seq,97,ATP,null)) ATP97,
-
- max(decode(seq,98,trim(item_id),null)) AltItemID98,max(decode(seq,98,ATP,null)) ATP98,
-
- max(decode(seq,99,trim(item_id),null)) AltItemID99,max(decode(seq,99,ATP,null)) ATP99,
-
- max(decode(seq,100,trim(item_id),null)) AltItemID100,max(decode(seq,100,ATP,null)) ATP100,
-
- max(decode(seq,101,trim(item_id),null)) AltItemID101,max(decode(seq,101,ATP,null)) ATP101,
-
- max(decode(seq,102,trim(item_id),null)) AltItemID102,max(decode(seq,102,ATP,null)) ATP102,
-
- max(decode(seq,103,trim(item_id),null)) AltItemID103,max(decode(seq,103,ATP,null)) ATP103,
-
- max(decode(seq,104,trim(item_id),null)) AltItemID104,max(decode(seq,104,ATP,null)) ATP104,
-
- max(decode(seq,105,trim(item_id),null)) AltItemID105,max(decode(seq,105,ATP,null)) ATP105,
-
- max(decode(seq,106,trim(item_id),null)) AltItemID106,max(decode(seq,106,ATP,null)) ATP106,
-
- max(decode(seq,107,trim(item_id),null)) AltItemID107,max(decode(seq,107,ATP,null)) ATP107,
-
- max(decode(seq,108,trim(item_id),null)) AltItemID108,max(decode(seq,108,ATP,null)) ATP108,
-
- max(decode(seq,109,trim(item_id),null)) AltItemID109,max(decode(seq,109,ATP,null)) ATP109,
-
- max(decode(seq,110,trim(item_id),null)) AltItemID110,max(decode(seq,110,ATP,null)) ATP110
-
- from
-
+(SELECT parent_key,CustomerName,SHIPNODE_KEY,
+ max(decode(seq,1,trim(item_id),null)) AltItemID1, max(decode(seq,1,ATP,null)) ATP1,
+ max(decode(seq,2,trim(item_id),null)) AltItemID2, max(decode(seq,2,ATP,null)) ATP2,
+ max(decode(seq,3,trim(item_id),null)) AltItemID3, max(decode(seq,3,ATP,null)) ATP3,
+ max(decode(seq,4,trim(item_id),null)) AltItemID4, max(decode(seq,4,ATP,null)) ATP4,
+ max(decode(seq,5,trim(item_id),null)) AltItemID5, max(decode(seq,5,ATP,null)) ATP5,
+ max(decode(seq,6,trim(item_id),null)) AltItemID6, max(decode(seq,6,ATP,null)) ATP6,
+ max(decode(seq,7,trim(item_id),null)) AltItemID7, max(decode(seq,7,ATP,null)) ATP7,
+ max(decode(seq,8,trim(item_id),null)) AltItemID8, max(decode(seq,8,ATP,null)) ATP8,
+ max(decode(seq,9,trim(item_id),null)) AltItemID9, max(decode(seq,9,ATP,null)) ATP9,
+ max(decode(seq,10,trim(item_id),null)) AltItemID10, max(decode(seq,10,ATP,null)) ATP10,
+ max(decode(seq,11,trim(item_id),null)) AltItemID11, max(decode(seq,11,ATP,null)) ATP11,
+ max(decode(seq,12,trim(item_id),null)) AltItemID12, max(decode(seq,12,ATP,null)) ATP12,
+ max(decode(seq,13,trim(item_id),null)) AltItemID13, max(decode(seq,13,ATP,null)) ATP13,
+ max(decode(seq,14,trim(item_id),null)) AltItemID14, max(decode(seq,14,ATP,null)) ATP14,
+ max(decode(seq,15,trim(item_id),null)) AltItemID15, max(decode(seq,15,ATP,null)) ATP15,
+ max(decode(seq,16,trim(item_id),null)) AltItemID16, max(decode(seq,16,ATP,null)) ATP16,
+ max(decode(seq,17,trim(item_id),null)) AltItemID17, max(decode(seq,17,ATP,null)) ATP17,
+ max(decode(seq,18,trim(item_id),null)) AltItemID18, max(decode(seq,18,ATP,null)) ATP18,
+ max(decode(seq,19,trim(item_id),null)) AltItemID19, max(decode(seq,19,ATP,null)) ATP19,
+ max(decode(seq,20,trim(item_id),null)) AltItemID20, max(decode(seq,20,ATP,null)) ATP20,
+ max(decode(seq,21,trim(item_id),null)) AltItemID21, max(decode(seq,21,ATP,null)) ATP21,
+ max(decode(seq,22,trim(item_id),null)) AltItemID22, max(decode(seq,22,ATP,null)) ATP22,
+ max(decode(seq,23,trim(item_id),null)) AltItemID23, max(decode(seq,23,ATP,null)) ATP23,
+ max(decode(seq,24,trim(item_id),null)) AltItemID24, max(decode(seq,24,ATP,null)) ATP24,
+ max(decode(seq,25,trim(item_id),null)) AltItemID25, max(decode(seq,25,ATP,null)) ATP25,
+ max(decode(seq,26,trim(item_id),null)) AltItemID26, max(decode(seq,26,ATP,null)) ATP26,
+ max(decode(seq,27,trim(item_id),null)) AltItemID27, max(decode(seq,27,ATP,null)) ATP27,
+ max(decode(seq,28,trim(item_id),null)) AltItemID28, max(decode(seq,28,ATP,null)) ATP28,
+ max(decode(seq,29,trim(item_id),null)) AltItemID29, max(decode(seq,29,ATP,null)) ATP29,
+ max(decode(seq,30,trim(item_id),null)) AltItemID30, max(decode(seq,30,ATP,null)) ATP30,
+ max(decode(seq,31,trim(item_id),null)) AltItemID31, max(decode(seq,31,ATP,null)) ATP31,
+ max(decode(seq,32,trim(item_id),null)) AltItemID32, max(decode(seq,32,ATP,null)) ATP32,
+ max(decode(seq,33,trim(item_id),null)) AltItemID33, max(decode(seq,33,ATP,null)) ATP33,
+ max(decode(seq,34,trim(item_id),null)) AltItemID34, max(decode(seq,34,ATP,null)) ATP34,
+ max(decode(seq,35,trim(item_id),null)) AltItemID35, max(decode(seq,35,ATP,null)) ATP35,
+ max(decode(seq,36,trim(item_id),null)) AltItemID36, max(decode(seq,36,ATP,null)) ATP36,
+ max(decode(seq,37,trim(item_id),null)) AltItemID37, max(decode(seq,37,ATP,null)) ATP37,
+ max(decode(seq,38,trim(item_id),null)) AltItemID38, max(decode(seq,38,ATP,null)) ATP38,
+ max(decode(seq,39,trim(item_id),null)) AltItemID39, max(decode(seq,39,ATP,null)) ATP39,
+ max(decode(seq,40,trim(item_id),null)) AltItemID40, max(decode(seq,40,ATP,null)) ATP40,
+ max(decode(seq,41,trim(item_id),null)) AltItemID41, max(decode(seq,41,ATP,null)) ATP41,
+ max(decode(seq,42,trim(item_id),null)) AltItemID42, max(decode(seq,42,ATP,null)) ATP42,
+ max(decode(seq,43,trim(item_id),null)) AltItemID43, max(decode(seq,43,ATP,null)) ATP43,
+ max(decode(seq,44,trim(item_id),null)) AltItemID44, max(decode(seq,44,ATP,null)) ATP44,
+ max(decode(seq,45,trim(item_id),null)) AltItemID45, max(decode(seq,45,ATP,null)) ATP45,
+ max(decode(seq,46,trim(item_id),null)) AltItemID46, max(decode(seq,46,ATP,null)) ATP46,
+ max(decode(seq,47,trim(item_id),null)) AltItemID47, max(decode(seq,47,ATP,null)) ATP47,
+ max(decode(seq,48,trim(item_id),null)) AltItemID48, max(decode(seq,48,ATP,null)) ATP48,
+ max(decode(seq,49,trim(item_id),null)) AltItemID49, max(decode(seq,49,ATP,null)) ATP49,
+ max(decode(seq,50,trim(item_id),null)) AltItemID50, max(decode(seq,50,ATP,null)) ATP50,
+ max(decode(seq,51,trim(item_id),null)) AltItemID51, max(decode(seq,51,ATP,null)) ATP51,
+ max(decode(seq,52,trim(item_id),null)) AltItemID52, max(decode(seq,52,ATP,null)) ATP52,
+ max(decode(seq,53,trim(item_id),null)) AltItemID53, max(decode(seq,53,ATP,null)) ATP53,
+ max(decode(seq,54,trim(item_id),null)) AltItemID54, max(decode(seq,54,ATP,null)) ATP54,
+ max(decode(seq,55,trim(item_id),null)) AltItemID55, max(decode(seq,55,ATP,null)) ATP55,
+ max(decode(seq,56,trim(item_id),null)) AltItemID56, max(decode(seq,56,ATP,null)) ATP56,
+ max(decode(seq,57,trim(item_id),null)) AltItemID57, max(decode(seq,57,ATP,null)) ATP57,
+ max(decode(seq,58,trim(item_id),null)) AltItemID58, max(decode(seq,58,ATP,null)) ATP58,
+ max(decode(seq,59,trim(item_id),null)) AltItemID59, max(decode(seq,59,ATP,null)) ATP59,
+ max(decode(seq,60,trim(item_id),null)) AltItemID60, max(decode(seq,60,ATP,null)) ATP60,
+ max(decode(seq,61,trim(item_id),null)) AltItemID61, max(decode(seq,61,ATP,null)) ATP61,
+ max(decode(seq,62,trim(item_id),null)) AltItemID62, max(decode(seq,62,ATP,null)) ATP62,
+ max(decode(seq,63,trim(item_id),null)) AltItemID63, max(decode(seq,63,ATP,null)) ATP63,
+ max(decode(seq,64,trim(item_id),null)) AltItemID64, max(decode(seq,64,ATP,null)) ATP64,
+ max(decode(seq,65,trim(item_id),null)) AltItemID65, max(decode(seq,65,ATP,null)) ATP65,
+ max(decode(seq,66,trim(item_id),null)) AltItemID66, max(decode(seq,66,ATP,null)) ATP66,
+ max(decode(seq,67,trim(item_id),null)) AltItemID67, max(decode(seq,67,ATP,null)) ATP67,
+ max(decode(seq,68,trim(item_id),null)) AltItemID68, max(decode(seq,68,ATP,null)) ATP68,
+ max(decode(seq,69,trim(item_id),null)) AltItemID69, max(decode(seq,69,ATP,null)) ATP69,
+ max(decode(seq,70,trim(item_id),null)) AltItemID70, max(decode(seq,70,ATP,null)) ATP70,
+ max(decode(seq,71,trim(item_id),null)) AltItemID71, max(decode(seq,71,ATP,null)) ATP71,
+ max(decode(seq,72,trim(item_id),null)) AltItemID72, max(decode(seq,72,ATP,null)) ATP72,
+ max(decode(seq,73,trim(item_id),null)) AltItemID73, max(decode(seq,73,ATP,null)) ATP73,
+ max(decode(seq,74,trim(item_id),null)) AltItemID74, max(decode(seq,74,ATP,null)) ATP74,
+ max(decode(seq,75,trim(item_id),null)) AltItemID75, max(decode(seq,75,ATP,null)) ATP75,
+ max(decode(seq,76,trim(item_id),null)) AltItemID76, max(decode(seq,76,ATP,null)) ATP76,
+ max(decode(seq,77,trim(item_id),null)) AltItemID77, max(decode(seq,77,ATP,null)) ATP77,
+ max(decode(seq,78,trim(item_id),null)) AltItemID78, max(decode(seq,78,ATP,null)) ATP78,
+ max(decode(seq,79,trim(item_id),null)) AltItemID79, max(decode(seq,79,ATP,null)) ATP79,
+ max(decode(seq,80,trim(item_id),null)) AltItemID80, max(decode(seq,80,ATP,null)) ATP80,
+ max(decode(seq,81,trim(item_id),null)) AltItemID81, max(decode(seq,81,ATP,null)) ATP81,
+ max(decode(seq,82,trim(item_id),null)) AltItemID82, max(decode(seq,82,ATP,null)) ATP82,
+ max(decode(seq,83,trim(item_id),null)) AltItemID83, max(decode(seq,83,ATP,null)) ATP83,
+ max(decode(seq,84,trim(item_id),null)) AltItemID84, max(decode(seq,84,ATP,null)) ATP84,
+ max(decode(seq,85,trim(item_id),null)) AltItemID85, max(decode(seq,85,ATP,null)) ATP85,
+ max(decode(seq,86,trim(item_id),null)) AltItemID86, max(decode(seq,86,ATP,null)) ATP86,
+ max(decode(seq,87,trim(item_id),null)) AltItemID87, max(decode(seq,87,ATP,null)) ATP87,
+ max(decode(seq,88,trim(item_id),null)) AltItemID88, max(decode(seq,88,ATP,null)) ATP88,
+ max(decode(seq,89,trim(item_id),null)) AltItemID89, max(decode(seq,89,ATP,null)) ATP89,
+ max(decode(seq,90,trim(item_id),null)) AltItemID90, max(decode(seq,90,ATP,null)) ATP90,
+ max(decode(seq,91,trim(item_id),null)) AltItemID91, max(decode(seq,91,ATP,null)) ATP91,
+ max(decode(seq,92,trim(item_id),null)) AltItemID92, max(decode(seq,92,ATP,null)) ATP92,
+ max(decode(seq,93,trim(item_id),null)) AltItemID93, max(decode(seq,93,ATP,null)) ATP93,
+ max(decode(seq,94,trim(item_id),null)) AltItemID94, max(decode(seq,94,ATP,null)) ATP94,
+ max(decode(seq,95,trim(item_id),null)) AltItemID95, max(decode(seq,95,ATP,null)) ATP95,
+ max(decode(seq,96,trim(item_id),null)) AltItemID96, max(decode(seq,96,ATP,null)) ATP96,
+ max(decode(seq,97,trim(item_id),null)) AltItemID97, max(decode(seq,97,ATP,null)) ATP97,
+ max(decode(seq,98,trim(item_id),null)) AltItemID98, max(decode(seq,98,ATP,null)) ATP98,
+ max(decode(seq,99,trim(item_id),null)) AltItemID99, max(decode(seq,99,ATP,null)) ATP99,
+ max(decode(seq,100,trim(item_id),null)) AltItemID100, max(decode(seq,100,ATP,null)) ATP100,
+ max(decode(seq,101,trim(item_id),null)) AltItemID101, max(decode(seq,101,ATP,null)) ATP101,
+ max(decode(seq,102,trim(item_id),null)) AltItemID102, max(decode(seq,102,ATP,null)) ATP102,
+ max(decode(seq,103,trim(item_id),null)) AltItemID103, max(decode(seq,103,ATP,null)) ATP103,
+ max(decode(seq,104,trim(item_id),null)) AltItemID104, max(decode(seq,104,ATP,null)) ATP104,
+ max(decode(seq,105,trim(item_id),null)) AltItemID105, max(decode(seq,105,ATP,null)) ATP105,
+ max(decode(seq,106,trim(item_id),null)) AltItemID106, max(decode(seq,106,ATP,null)) ATP106,
+ max(decode(seq,107,trim(item_id),null)) AltItemID107, max(decode(seq,107,ATP,null)) ATP107,
+ max(decode(seq,108,trim(item_id),null)) AltItemID108, max(decode(seq,108,ATP,null)) ATP108,
+ max(decode(seq,109,trim(item_id),null)) AltItemID109, max(decode(seq,109,ATP,null)) ATP109,
+ max(decode(seq,110,trim(item_id),null)) AltItemID110, max(decode(seq,110,ATP,null)) ATP110
+ from
(select
-
INV.CustomerName,INV.SHIPNODE_KEY,INV.item_id,INV.item_key,INV.ATP,
-
a.parent_key,A.associated_key,A.priority,
-
row_number() OVER (PARTITION BY customerName,Shipnode_key,parent_key ORDER BY priority NULLS LAST ) seq
-
from selprod.inventory INV,selprod.YFS_association A
-
where a.associated_key=INV.item_key
-
)
-
group by parent_key,CustomerName,SHIPNODE_KEY
-
),
-
backorder as
-
(select /*+ LEADING ( ORS ) use_nl(ORS,OL) use_nl(ORS,H) */
-
h.order_date as OrderDate,
-
h.extn_service_request_no as ServiceRequestNo,
-
h.order_no as OutboundNumber,
-
h.extn_reference_1 as DellRequestNumberref1,
-
OL.ORDER_LINE_KEY as OrderLineKey,
-
OL.SHIPNODE_KEY as shipnode_key,
-
OrigIT.item_id as item_id,
-
OrigIT.item_key as item_key
-
from
-
selprod.YFS_ORDER_RELEASE_STATUS ORS,
-
selprod.YFS_ORDER_LINE OL,
-
selprod.YFS_ORDER_HEADER H,
-
selprod.YFS_ITEM OrigIT
-
where
-
ORS.status='1300'
-
and status_quantity>0
-
and ORS.Order_Line_Key=OL.ORDER_LINE_KEY
-
and ORS.Order_Header_Key=h.order_header_key
-
and h.seller_organization_code='DELL'
-
and h.order_no like('OB%')
-
and ol.item_id=OrigIT.item_id
-
)
-
-Select B.OrderDate,trim(B.ServiceRequestNo)ServiceRequestNo,trim(B.OutboundNumber)OutboundNumber,trim(B.DellRequestNumberref1) DellRequestNumberRef1,trim(B.OrderLineKey)OrderLineKey,trim(B.shipnode_key) ShipnodeKey,trim(B.item_id) ItemId, nvl(I.ATP,0) as OrigATP,
-
-FI.AltItemID1, FI.ATP1,FI.AltItemID2, FI.ATP2,FI.AltItemID3, FI.ATP3,FI.AltItemID4, FI.ATP4,FI.AltItemID5, FI.ATP5,FI.AltItemID6, FI.ATP6,FI.AltItemID7, FI.ATP7,FI.AltItemID8, FI.ATP8,FI.AltItemID9, FI.ATP9,FI.AltItemID10, FI.ATP10,FI.AltItemID11, FI.ATP11,FI.AltItemID12, FI.ATP12,FI.AltItemID13, FI.ATP13,FI.AltItemID14, FI.ATP14,FI.AltItemID15, FI.ATP15,FI.AltItemID16, FI.ATP16,FI.AltItemID17, FI.ATP17,FI.AltItemID18, FI.ATP18,FI.AltItemID19, FI.ATP19,FI.AltItemID20, FI.ATP20,FI.AltItemID21, FI.ATP21,FI.AltItemID22, FI.ATP22,FI.AltItemID23, FI.ATP23,FI.AltItemID24, FI.ATP24,FI.AltItemID25, FI.ATP25,FI.AltItemID26, FI.ATP26,FI.AltItemID27, FI.ATP27,FI.AltItemID28, FI.ATP28,FI.AltItemID29, FI.ATP29,FI.AltItemID30, FI.ATP30,FI.AltItemID31, FI.ATP31,FI.AltItemID32, FI.ATP32,FI.AltItemID33, FI.ATP33,FI.AltItemID34, FI.ATP34,FI.AltItemID35, FI.ATP35,FI.AltItemID36, FI.ATP36,FI.AltItemID37, FI.ATP37,FI.AltItemID38, FI.ATP38,FI.AltItemID39, FI.ATP39,FI.AltItemID40, FI.ATP40,FI.AltItemID41, FI.ATP41,FI.AltItemID42, FI.ATP42,FI.AltItemID43, FI.ATP43,FI.AltItemID44, FI.ATP44,FI.AltItemID45, FI.ATP45,FI.AltItemID46, FI.ATP46,FI.AltItemID47, FI.ATP47,FI.AltItemID48, FI.ATP48,FI.AltItemID49, FI.ATP49,FI.AltItemID50, FI.ATP50,FI.AltItemID51, FI.ATP51,FI.AltItemID52, FI.ATP52,FI.AltItemID53, FI.ATP53,FI.AltItemID54, FI.ATP54,FI.AltItemID55, FI.ATP55,FI.AltItemID56, FI.ATP56,FI.AltItemID57, FI.ATP57,FI.AltItemID58, FI.ATP58,FI.AltItemID59, FI.ATP59,FI.AltItemID60, FI.ATP60,FI.AltItemID61, FI.ATP61,FI.AltItemID62, FI.ATP62,FI.AltItemID63, FI.ATP63,FI.AltItemID64, FI.ATP64,FI.AltItemID65, FI.ATP65,FI.AltItemID66, FI.ATP66,FI.AltItemID67, FI.ATP67,FI.AltItemID68, FI.ATP68,FI.AltItemID69, FI.ATP69,FI.AltItemID70, FI.ATP70,FI.AltItemID71, FI.ATP71,FI.AltItemID72, FI.ATP72,FI.AltItemID73, FI.ATP73,FI.AltItemID74, FI.ATP74,FI.AltItemID75, FI.ATP75,FI.AltItemID76, FI.ATP76,FI.AltItemID77, FI.ATP77,FI.AltItemID78, FI.ATP78,FI.AltItemID79, FI.ATP79,FI.AltItemID80, FI.ATP80
-
-,FI.AltItemID81, FI.ATP81, FI.AltItemID82, FI.ATP82, FI.AltItemID83, FI.ATP83, FI.AltItemID84, FI.ATP84, FI.AltItemID85, FI.ATP85, FI.AltItemID86, FI.ATP86, FI.AltItemID87, FI.ATP87, FI.AltItemID88, FI.ATP88, FI.AltItemID89, FI.ATP89, FI.AltItemID90, FI.ATP90, FI.AltItemID91, FI.ATP91, FI.AltItemID92, FI.ATP92, FI.AltItemID93, FI.ATP93, FI.AltItemID94, FI.ATP94, FI.AltItemID95, FI.ATP95, FI.AltItemID96, FI.ATP96, FI.AltItemID97, FI.ATP97, FI.AltItemID98, FI.ATP98, FI.AltItemID99, FI.ATP99, FI.AltItemID100, FI.ATP100, FI.AltItemID101, FI.ATP101, FI.AltItemID102, FI.ATP102, FI.AltItemID103, FI.ATP103, FI.AltItemID104, FI.ATP104, FI.AltItemID105, FI.ATP105, FI.AltItemID106, FI.ATP106, FI.AltItemID107, FI.ATP107, FI.AltItemID108, FI.ATP108, FI.AltItemID109, FI.ATP109, FI.AltItemID110, FI.ATP110
-
+Select B.OrderDate,
+ trim(B.ServiceRequestNo)ServiceRequestNo,
+ trim(B.OutboundNumber)OutboundNumber,
+ trim(B.DellRequestNumberref1) DellRequestNumberRef1,
+ trim(B.OrderLineKey)OrderLineKey,
+ trim(B.shipnode_key) ShipnodeKey,
+ trim(B.item_id) ItemId,
+ nvl(I.ATP, 0) as OrigATP,
+ FI.AltItemID1, FI.ATP1,
+ FI.AltItemID2, FI.ATP2,
+ FI.AltItemID3, FI.ATP3,
+ FI.AltItemID4, FI.ATP4,
+ FI.AltItemID5, FI.ATP5,
+ FI.AltItemID6, FI.ATP6,
+ FI.AltItemID7, FI.ATP7,
+ FI.AltItemID8, FI.ATP8,
+ FI.AltItemID9, FI.ATP9,
+ FI.AltItemID10, FI.ATP10,
+ FI.AltItemID11, FI.ATP11,
+ FI.AltItemID12, FI.ATP12,
+ FI.AltItemID13, FI.ATP13,
+ FI.AltItemID14, FI.ATP14,
+ FI.AltItemID15, FI.ATP15,
+ FI.AltItemID16, FI.ATP16,
+ FI.AltItemID17, FI.ATP17,
+ FI.AltItemID18, FI.ATP18,
+ FI.AltItemID19, FI.ATP19,
+ FI.AltItemID20, FI.ATP20,
+ FI.AltItemID21, FI.ATP21,
+ FI.AltItemID22, FI.ATP22,
+ FI.AltItemID23, FI.ATP23,
+ FI.AltItemID24, FI.ATP24,
+ FI.AltItemID25, FI.ATP25,
+ FI.AltItemID26, FI.ATP26,
+ FI.AltItemID27, FI.ATP27,
+ FI.AltItemID28, FI.ATP28,
+ FI.AltItemID29, FI.ATP29,
+ FI.AltItemID30, FI.ATP30,
+ FI.AltItemID31, FI.ATP31,
+ FI.AltItemID32, FI.ATP32,
+ FI.AltItemID33, FI.ATP33,
+ FI.AltItemID34, FI.ATP34,
+ FI.AltItemID35, FI.ATP35,
+ FI.AltItemID36, FI.ATP36,
+ FI.AltItemID37, FI.ATP37,
+ FI.AltItemID38, FI.ATP38,
+ FI.AltItemID39, FI.ATP39,
+ FI.AltItemID40, FI.ATP40,
+ FI.AltItemID41, FI.ATP41,
+ FI.AltItemID42, FI.ATP42,
+ FI.AltItemID43, FI.ATP43,
+ FI.AltItemID44, FI.ATP44,
+ FI.AltItemID45, FI.ATP45,
+ FI.AltItemID46, FI.ATP46,
+ FI.AltItemID47, FI.ATP47,
+ FI.AltItemID48, FI.ATP48,
+ FI.AltItemID49, FI.ATP49,
+ FI.AltItemID50, FI.ATP50,
+ FI.AltItemID51, FI.ATP51,
+ FI.AltItemID52, FI.ATP52,
+ FI.AltItemID53, FI.ATP53,
+ FI.AltItemID54, FI.ATP54,
+ FI.AltItemID55, FI.ATP55,
+ FI.AltItemID56, FI.ATP56,
+ FI.AltItemID57, FI.ATP57,
+ FI.AltItemID58, FI.ATP58,
+ FI.AltItemID59, FI.ATP59,
+ FI.AltItemID60, FI.ATP60,
+ FI.AltItemID61, FI.ATP61,
+ FI.AltItemID62, FI.ATP62,
+ FI.AltItemID63, FI.ATP63,
+ FI.AltItemID64, FI.ATP64,
+ FI.AltItemID65, FI.ATP65,
+ FI.AltItemID66, FI.ATP66,
+ FI.AltItemID67, FI.ATP67,
+ FI.AltItemID68, FI.ATP68,
+ FI.AltItemID69, FI.ATP69,
+ FI.AltItemID70, FI.ATP70,
+ FI.AltItemID71, FI.ATP71,
+ FI.AltItemID72, FI.ATP72,
+ FI.AltItemID73, FI.ATP73,
+ FI.AltItemID74, FI.ATP74,
+ FI.AltItemID75, FI.ATP75,
+ FI.AltItemID76, FI.ATP76,
+ FI.AltItemID77, FI.ATP77,
+ FI.AltItemID78, FI.ATP78,
+ FI.AltItemID79, FI.ATP79,
+ FI.AltItemID80, FI.ATP80,
+ FI.AltItemID81, FI.ATP81,
+ FI.AltItemID82, FI.ATP82,
+ FI.AltItemID83, FI.ATP83,
+ FI.AltItemID84, FI.ATP84,
+ FI.AltItemID85, FI.ATP85,
+ FI.AltItemID86, FI.ATP86,
+ FI.AltItemID87, FI.ATP87,
+ FI.AltItemID88, FI.ATP88,
+ FI.AltItemID89, FI.ATP89,
+ FI.AltItemID90, FI.ATP90,
+ FI.AltItemID91, FI.ATP91,
+ FI.AltItemID92, FI.ATP92,
+ FI.AltItemID93, FI.ATP93,
+ FI.AltItemID94, FI.ATP94,
+ FI.AltItemID95, FI.ATP95,
+ FI.AltItemID96, FI.ATP96,
+ FI.AltItemID97, FI.ATP97,
+ FI.AltItemID98, FI.ATP98,
+ FI.AltItemID99, FI.ATP99,
+ FI.AltItemID100, FI.ATP100,
+ FI.AltItemID101, FI.ATP101,
+ FI.AltItemID102, FI.ATP102,
+ FI.AltItemID103, FI.ATP103,
+ FI.AltItemID104, FI.ATP104,
+ FI.AltItemID105, FI.ATP105,
+ FI.AltItemID106, FI.ATP106,
+ FI.AltItemID107, FI.ATP107,
+ FI.AltItemID108, FI.ATP108,
+ FI.AltItemID109, FI.ATP109,
+ FI.AltItemID110, FI.ATP110
from
-
backorder B,
-
Inventory I,
-
FlatInventory FI
-
where
-
B.item_id=I.item_id(+)
-
and B.Shipnode_Key=I.SHIPNODE_KEY(+)
-
and B.item_key=FI.parent_key(+)
-
and B.shipnode_key=FI.shipnode_key(+)
-
and (nvl(I.ATP,0)<>0 or ATP1<>0)
-
)
-
;
Modified: branches/tora-trotl/sandbox/tests/condition01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/condition01.sql 2011-05-05 23:39:31 UTC (rev 3957)
+++ branches/tora-trotl/sandbox/tests/condition01.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -1,5 +1,7 @@
SELECT department_id, last_name, salary
- FROM employees x
- WHERE salary > (SELECT AVG(salary) FROM employees WHERE x.department_id = department_id)
- ORDER BY department_id
+ FROM employees x
+ WHERE salary > (SELECT AVG(salary)
+ FROM employees
+ WHERE x.department_id = department_id)
+ ORDER BY department_id
;
Modified: branches/tora-trotl/sandbox/tests/condition02.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/condition02.sql 2011-05-05 23:39:31 UTC (rev 3957)
+++ branches/tora-trotl/sandbox/tests/condition02.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -1,6 +1,6 @@
SELECT *
FROM EMPLOYEES X
- WHERE SALARY > (SELECT AVG(SALARY) FROM Y)
+ WHERE SALARY > (SELECT AVG(SALARY) FROM X)
AND 1 = 1
AND HIREDATE = SYSDATE
AND TO_YMINTERVAL('01-00') < SYSDATE
Added: branches/tora-trotl/sandbox/tests/condition04.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/condition04.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/condition04.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,34 @@
+SELECT *
+FROM T
+WHERE
+(
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ ( TYPE = '2' ) OR ( TYPE = '3' )
+ ) AND ( T.CDE < 20 )
+ ) AND ( T.SE = 'XXX' )
+ ) AND ( T.ID = '000000000002' )
+ ) AND ( ( T.ATTR_1 IS NULL ) OR ( T.ATTR_1 = '*' ) )
+ ) AND ( ( T.ATTR_2 IS NULL ) OR ( T.ATTR_2 = '*' ) )
+ ) AND ( ( T.ATTR_3 IS NULL ) OR ( T.ATTR_3 = '*' ) )
+ ) AND ( ( T.ATTR_4 IS NULL ) OR ( T.ATTR_4 = '*' ) )
+ ) AND ( ( T.ATTR_5 IS NULL ) OR ( T.ATTR_5 = '*' ) )
+ ) AND ( ( T.ITYPE IS NULL ) OR ( T.ITYPE = '*' ) )
+ ) AND ( ( T.INBR IS NULL ) OR ( T.INBR = '*' ) )
+ ) AND ( ( T.STAT = '01' ) OR ( T.STAT = '*' ) )
+ ) AND ( ( T.ORGN IS NULL ) OR ( T.ORGN = '*' ) )
+ ) AND ( T.MBR = '0000000000001' )
+ ) AND ( T.NBR IS NULL )
+)
Added: branches/tora-trotl/sandbox/tests/condition05.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/condition05.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/condition05.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,18 @@
+SELECT *
+FROM T
+WHERE
+ ( T.TYPE = '2' ) OR ( T.TYPE = '3' )
+ AND T.CDE < 20
+ AND T.SE = 'XXX'
+ AND T.ID = '000000000002'
+ AND ( ( T.SKU_ATTR_1 IS NULL ) OR ( T.SKU_ATTR_1 = '*' ) )
+ AND ( ( T.SKU_ATTR_2 IS NULL ) OR ( T.SKU_ATTR_2 = '*' ) )
+ AND ( ( T.SKU_ATTR_3 IS NULL ) OR ( T.SKU_ATTR_3 = '*' ) )
+ AND ( ( T.SKU_ATTR_4 IS NULL ) OR ( T.SKU_ATTR_4 = '*' ) )
+ AND ( ( T.SKU_ATTR_5 IS NULL ) OR ( T.SKU_ATTR_5 = '*' ) )
+ AND ( ( T.ITYPE IS NULL ) OR ( T.ITYPE = '*' ) )
+ AND ( ( T.BNBR IS NULL ) OR ( T.BNBR = '*' ) )
+ AND ( ( T.STAT = '01' ) OR ( T.STAT = '*' ) )
+ AND ( ( T.ORGN IS NULL ) OR ( T.ORGN = '*' ) )
+ AND ( T.MBR = '0000000000001' )
+ AND ( T.NBR IS NULL )
Added: branches/tora-trotl/sandbox/tests/condition06.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/condition06.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/condition06.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,14 @@
+SELECT *
+FROM T1, T2
+WHERE (TRUNC(T1.timestamp) BETWEEN to_date('110226','YYMMDD') AND to_date('110326','YYMMDD'))
+AND T1.CODE(+) = 'CN'
+AND T1.ID(+)=T2.ID
+AND T1.CID=T2.CID
+AND T1.MID = 1245714070376993504
+AND T1.TMST >= to_date('110226','YYMMDD')
+-- NOTE: This is possible too "column_spec outer_join_sign conditional_operator
+AND SHIPPER.ALT_PARTY_CODE(+) IS NULL
+AND T2.CODE(+) = 'SH'
+AND T1.SID(+)=T2.SID
+AND ( ( T1.SCODE LIKE 'MMM' AND T2.SCODE LIKE 'XAX' ) )
+;
\ No newline at end of file
Added: branches/tora-trotl/sandbox/tests/condition07.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/condition07.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/condition07.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,6 @@
+SELECT *
+FROM append
+WHERE
+-- note space between '>' and '='
+(length(w.numer) > = 8)
+;
\ No newline at end of file
Added: branches/tora-trotl/sandbox/tests/condition08.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/condition08.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/condition08.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,6 @@
+SELECT *
+FROM "P"
+WHERE
+-- NOTE there are no parens around 231092
+( ( "P"."ID" IN 231092 ) )
+;
\ No newline at end of file
Added: branches/tora-trotl/sandbox/tests/condition09.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/condition09.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/condition09.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,17 @@
+SELECT
+ SUM(NVL(PL.QTY,0))
+ FROM
+ OLINE OL,
+ PLINE PL,
+ BLOCATION BL
+ WHERE
+ OL.ID = PL.ID
+ AND PL.NO = PL.NO
+ AND BL.ID = PL.ID
+ AND
+ (
+ (SELECT COUNT(*) FROM LA.SA WHERE PL.ID LIKE SA.BID) > 0
+ OR
+ (SELECT COUNT(*) FROM LA.SA WHERE BL.ID LIKE SA.ID) > 0
+ )
+;
\ No newline at end of file
Added: branches/tora-trotl/sandbox/tests/condition10.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/condition10.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/condition10.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,15 @@
+SELECT department_id, last_name, salary
+ FROM employees x
+ WHERE
+ 1 = 1
+ AND
+ (
+ (
+ HI
+ )
+ >
+ (
+ .1 * T.ROWCNT
+ )
+ )
+;
Added: branches/tora-trotl/sandbox/tests/condition11.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/condition11.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/condition11.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,7 @@
+SELECT DISTINCT X
+FROM X,Y,Z
+WHERE
+ X.id = Z.id (+)
+and nvl(X.cid, '^') = nvl(Y.clientid (+), '^')
+and 0 = Lib.SKU(X.sid, nvl(Z.cid, '^'))
+;
Added: branches/tora-trotl/sandbox/tests/condition12.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/condition12.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/condition12.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,11 @@
+DELETE FROM WMI.ERROR
+WHERE
+ CID <> RID
+ AND RID NOT IN
+ (
+ (SELECT DISTINCT RID FROM WMI.SERVICE )
+ UNION
+ (SELECT DISTINCT RID FROM WMI.PROCESS )
+ )
+ AND TIMESTAMP <= 1298505600000
+;
Added: branches/tora-trotl/sandbox/tests/datetime02.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/datetime02.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/datetime02.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,30 @@
+SELECT
+ dbin.db_name,
+ dbin.instance_name,
+ dbin.version,
+ CASE WHEN s1.startup_time = s2.startup_time THEN 0 ELSE 1 END as bounce,
+ CAST(s1.end_interval_time AS DATE) as begin_time,
+ CAST(s2.end_interval_time AS DATE) as end_time,
+ ROUND((cast( (case when s2.end_interval_time > s1.end_interval_time then s2.end_interval_time else s1.end_interval_time end) as date)
+ - cast(s1.end_interval_time as date)) * 86400) as int_secs,
+ CASE WHEN (s1.status <> 0 OR s2.status <> 0) THEN 1 ELSE 0 END as err_detect,
+ round( greatest( (extract(day from s2.flush_elapsed) * 86400)
+ + (extract(hour from s2.flush_elapsed) * 3600)
+ + (extract(minute from s2.flush_elapsed) * 60)
+ + extract(second from s2.flush_elapsed),
+ (extract(day from s1.flush_elapsed) * 86400)
+ + (extract(hour from s1.flush_elapsed) * 3600)
+ + (extract(minute from s1.flush_elapsed) * 60)
+ + extract(second from s1.flush_elapsed),0 )
+ ) as max_flush_secs
+FROM WRM$_SNAPSHOT s1 , WRM$_DATABASE_INSTANCE dbin , WRM$_SNAPSHOT s2
+WHERE s1.dbid = :dbid
+AND s2.dbid = :dbid
+AND s1.instance_number = :inst_num
+AND s2.instance_number = :inst_num
+AND s1.snap_id = :bid
+AND s2.snap_id = :eid
+AND dbin.dbid = s1.dbid
+AND dbin.instance_number = s1.instance_number
+AND dbin.startup_time = s1.startup_time
+;
Added: branches/tora-trotl/sandbox/tests/datetime03.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/datetime03.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/datetime03.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,3 @@
+SELECT
+TIMESTAMP '2009-10-29 01:30:00' AT TIME ZONE 'US/Pacific'FROM DUAL
+;
Added: branches/tora-trotl/sandbox/tests/datetime04.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/datetime04.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/datetime04.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,4 @@
+SELECT
+TIMESTAMP '2009-10-29 01:30:00'
+FROM DUAL
+;
Added: branches/tora-trotl/sandbox/tests/for_update08.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/for_update08.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/for_update08.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,6 @@
+select SU.TType ,SU.CID ,SU.S_ID ,SESSIONTIMEZONE
+from SKU SU
+where (NVL(SU.Up,'N')='N' and SU.TType=:b0)
+for update of SU.Up
+order by SU.D
+;
\ No newline at end of file
Added: branches/tora-trotl/sandbox/tests/function02.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/function02.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/function02.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1 @@
+SELECT SET(X) FROM DUAL;
Added: branches/tora-trotl/sandbox/tests/function03.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/function03.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/function03.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,4 @@
+SELECT trim(both from con.KE)
+FROM DUAL
+;
+
Added: branches/tora-trotl/sandbox/tests/function04.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/function04.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/function04.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1 @@
+select LNNVL( 2 > 1) FROM DUAL;
Added: branches/tora-trotl/sandbox/tests/groupby01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/groupby01.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/groupby01.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,9 @@
+SELECT :B3 AS L_SNAP_ID , :B2 AS P_DBID , :B1 AS P_INSTANCE_NUMBER , NVL(PID, -9) PID , NVL(SERIAL#, -9) SERIAL# , DECODE(PID, NULL, NULL, MAX(SPID)) SPID ,
+ DECODE(PID, NULL, NULL, MAX(PROGRAM)) PROGRAM , DECODE(PID, NULL, NULL, MAX(BACKGROUND)) BACKGROUND , SUM(PGA_USED_MEM) PGA_USED_MEM ,
+ SUM(PGA_ALLOC_MEM) PGA_ALLOC_MEM , SUM(PGA_FREEABLE_MEM) PGA_FREEABLE_MEM , MAX(PGA_ALLOC_MEM) MAX_PGA_ALLOC_MEM , MAX(PGA_MAX_MEM) MAX_PGA_MAX_MEM ,
+ DECODE(PID, NULL, AVG(PGA_ALLOC_MEM), NULL) AVG_PGA_ALLOC_MEM , DECODE(PID, NULL, STDDEV(PGA_ALLOC_MEM), NULL) STDDEV_PGA_ALLOC_MEM ,
+ DECODE(PID, NULL, COUNT(PID), NULL) NUM_PROCESSES
+ FROM V$PROCESS
+ WHERE PROGRAM != 'PSEUDO'
+ GROUP BY GROUPING SETS ( (PID, SERIAL#), () )
+;
Added: branches/tora-trotl/sandbox/tests/groupby02.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/groupby02.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/groupby02.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,11 @@
+SELECT
+ DECODE(PID, NULL, NULL, MAX(PROGRAM)) PROGRAM , DECODE(PID, NULL, NULL, MAX(BACKGROUND)) BACKGROUND , SUM(PGA_USED_MEM) PGA_USED_MEM ,
+ SUM(PGA_ALLOC_MEM) PGA_ALLOC_MEM , SUM(PGA_FREEABLE_MEM) PGA_FREEABLE_MEM , MAX(PGA_ALLOC_MEM) MAX_PGA_ALLOC_MEM , MAX(PGA_MAX_MEM) MAX_PGA_MAX_MEM ,
+ DECODE(PID, NULL, AVG(PGA_ALLOC_MEM), NULL) AVG_PGA_ALLOC_MEM , DECODE(PID, NULL, STDDEV(PGA_ALLOC_MEM), NULL) STDDEV_PGA_ALLOC_MEM ,
+ DECODE(PID, NULL, COUNT(PID), NULL) NUM_PROCESSES
+FROM V$PROCESS
+WHERE PROGRAM != 'PSEUDO'
+GROUP BY GROUPING SETS ( (), ((PID+1), SERIAL#) )
+;
+
+
\ No newline at end of file
Added: branches/tora-trotl/sandbox/tests/groupby03.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/groupby03.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/groupby03.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,4 @@
+SELECT * FROM X
+GROUP BY GROUPING SETS
+( A, 1 )
+;
Added: branches/tora-trotl/sandbox/tests/groupby04.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/groupby04.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/groupby04.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,3 @@
+SELECT * FROM X
+GROUP BY GROUPING SETS
+( (A),1 );
Added: branches/tora-trotl/sandbox/tests/groupby05.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/groupby05.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/groupby05.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,4 @@
+SELECT * FROM X
+GROUP BY GROUPING SETS
+( ((A),B), ((A),B) )
+;
Added: branches/tora-trotl/sandbox/tests/interval02.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/interval02.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/interval02.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1 @@
+select INTERVAL '42' DAY from dual;
Added: branches/tora-trotl/sandbox/tests/join17.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/join17.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/join17.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,7 @@
+SELECT *
+FROM HDR A
+INNER JOIN SH S
+INNER JOIN CA C
+ON C.ID = S.ID
+ON A.VA = S.VA
+;
Modified: branches/tora-trotl/sandbox/tests/merge01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/merge01.sql 2011-05-05 23:39:31 UTC (rev 3957)
+++ branches/tora-trotl/sandbox/tests/merge01.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -1,9 +1,13 @@
MERGE INTO bonuses D
-USING (SELECT employee_id, salary, department_id FROM employees
-WHERE department_id = 80) S
+USING
+(
+ SELECT employee_id, salary, department_id
+ FROM employees
+ WHERE department_id = 80
+) S
ON (D.employee_id = S.employee_id)
-WHEN MATCHED THEN UPDATE SET
-D.bonus = D.bonus + S.salary*.01
+WHEN MATCHED THEN
+UPDATE SET D.bonus = D.bonus + S.salary*.01
DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*.01)
Added: branches/tora-trotl/sandbox/tests/merge02.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/merge02.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/merge02.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,18 @@
+MERGE /*+ dynamic_sampling(mm 4) dynamic_sampling_est_cdn(mm)
+ dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */
+
+INTO SYS.MON_MODS_ALL$ MM
+USING
+(
+ SELECT DECODE(GROUPING_ID(TP.BO#,TSP.POBJ#,M.OBJ#),3,TP.BO#,1,TSP.POBJ#,M.OBJ#) OBJ#, SUM(M.INSERTS) INSERTS, SUM(M.UPDATES) UPDATES, SUM(M.DELETES) DELETES,
+ DECODE(SUM(BITAND(M.FLAGS,1)),0,0,1) +DECODE(SUM(BITAND(M.FLAGS,2)),0,0,2) +DECODE(SUM(BITAND(M.FLAGS,4)),0,0,4) FLAGS, SUM(M.DROP_SEGMENTS) DROP_SEGMENTS
+ FROM SYS.MON_MODS$ M, SYS.TABCOMPART$ TP, SYS.TABSUBPART$ TSP
+ WHERE M.OBJ# = TSP.OBJ# AND TP.OBJ# = TSP.POBJ#
+ GROUP BY ROLLUP(TP.BO#,TSP.POBJ#,M.OBJ#) HAVING GROUPING_ID(TP.BO#,TSP.POBJ#,M.OBJ#) < 7
+) V ON
+(MM.OBJ# = V.OBJ#)
+WHEN MATCHED THEN
+UPDATE SET MM.INSERTS = MM.INSERTS + V.INSERTS, MM.UPDATES = MM.UPDATES + V.UPDATES, MM.DELETES = MM.DELETES + V.DELETES,
+MM.FLAGS = MM.FLAGS + V.FLAGS - BITAND(MM.FLAGS,V.FLAGS) , MM.DROP_SEGMENTS = MM.DROP_SEGMENTS + V.DROP_SEGMENTS
+WHEN NOT MATCHED THEN INSERT VALUES (V.OBJ#, V.INSERTS, V.UPDATES, V.DELETES, SYSDATE, V.FLAGS, V.DROP_SEGMENTS)
+;
\ No newline at end of file
Added: branches/tora-trotl/sandbox/tests/object_access.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/object_access.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/object_access.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1,18 @@
+SELECT
+EXTRACTVALUE(VALUE(T), '/select_list_item/pos') + 1 POS,
+EXTRACTVALUE(VALUE(T), '/select_list_item/value') RES,
+EXTRACTVALUE(VALUE(T), '/select_list_item/nonnulls') NONNULLS,
+EXTRACTVALUE(VALUE(T), '/select_list_item/ndv') NDV,
+EXTRACTVALUE(VALUE(T), '/select_list_item/split') SPLIT,
+EXTRACTVALUE(VALUE(T), '/select_list_item/rsize') RSIZE,
+EXTRACTVALUE(VALUE(T), '/select_list_item/rowcnt') ROWCNT,
+EXTRACT(VALUE(T), '/select_list_item/hash_val').GETCLOBVAL() HASHVAL
+FROM
+TABLE
+(
+ XMLSEQUENCE
+ (
+ EXTRACT(:B1 , '/process_result/select_list_item')
+ )
+) T
+;
Added: branches/tora-trotl/sandbox/tests/parens01.sql
===================================================================
--- branches/tora-trotl/sandbox/tests/parens01.sql (rev 0)
+++ branches/tora-trotl/sandbox/tests/parens01.sql 2011-05-13 14:35:40 UTC (rev 3958)
@@ -0,0 +1 @@
+SELECT (((((((((3)))))))...
[truncated message content] |