Hi,
I'm trying to use the ANSI syntax for a SQL statement involving nested table columns, but I can't seem to get it right.
Below is the test case to reproduce. My DB version is 11.2.0.3.
Connecté à :
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> alter session set nls_language=english;
Session altered.
SQL>
SQL> -- Create the types
SQL> CREATE OR REPLACE TYPE WEEK_QTY_TYP AS OBJECT(WEEK_DATE DATE, QTY NUMBER);
2 /
Type created.
SQL> CREATE OR REPLACE TYPE WEEK_QTY_NT_TYP AS TABLE OF WEEK_QTY_TYP;
2 /
Type created.
SQL> -- Create the table
SQL> CREATE TABLE T(ID NUMBER,
2 WEEK_ORDER_QTIES WEEK_QTY_NT_TYP,
3 WEEK_SALE_QTIES WEEK_QTY_NT_TYP
4 )
5 NESTED TABLE WEEK_ORDER_QTIES STORE AS WEEK_ORDER_QTIES_NT
6 NESTED TABLE WEEK_SALE_QTIES STORE AS WEEK_SALE_QTIES_NT;
Table created.
SQL>
SQL> -- Insert few rows
SQL> INSERT INTO T VALUES(1,
2 WEEK_QTY_NT_TYP(WEEK_QTY_TYP(TO_DATE('06/10/2014', 'dd/mm/yyyy') , 1), WEEK_QTY_TYP(TO_DATE('06/10/2014', 'dd/mm/yyyy') , 2)),
3 WEEK_QTY_NT_TYP(WEEK_QTY_TYP(TO_DATE('06/10/2014', 'dd/mm/yyyy') , 3), WEEK_QTY_TYP(TO_DATE('06/10/2014', 'dd/mm/yyyy') , 4))
4 );
1 row created.
SQL>
SQL> INSERT INTO T VALUES(2,
2 WEEK_QTY_NT_TYP(WEEK_QTY_TYP(TO_DATE('20/10/2014', 'dd/mm/yyyy') , 5), WEEK_QTY_TYP(TO_DATE('20/10/2014', 'dd/mm/yyyy') , 6)),
3 WEEK_QTY_NT_TYP(WEEK_QTY_TYP(TO_DATE('20/10/2014', 'dd/mm/yyyy') , 7), WEEK_QTY_TYP(TO_DATE('20/10/2014', 'dd/mm/yyyy') , 8))
4 );
1 row created.
SQL>
SQL> INSERT INTO T VALUES(3,
2 WEEK_QTY_NT_TYP(WEEK_QTY_TYP(TO_DATE('27/10/2014', 'dd/mm/yyyy') , 9), WEEK_QTY_TYP(TO_DATE('27/10/2014', 'dd/mm/yyyy') , 10)),
3 WEEK_QTY_NT_TYP(WEEK_QTY_TYP(TO_DATE('03/11/2014', 'dd/mm/yyyy') , 11), WEEK_QTY_TYP(TO_DATE('03/11/2014', 'dd/mm/yyyy') , 12))
4 );
1 row created.
SQL>
Now, I want to show for the weeks returned by the "weeks" block below the corresponding data in my table t for id = 3. I want to show the weeks even if there's no match in t.
The following query using "old style" syntax works fine and returns the expected result:
SQL> with weeks AS (select TO_DATE('06/10/2014', 'dd/mm/yyyy') + 7 * (level-1) week_date
2 from dual
3 connect by level <= 5
4 )
5 select w.week_date,
6 v.id,
7 oq.week_date,
8 oq.qty,
9 sq.week_date,
10 sq.qty
11 from weeks w,
12 (SELECT * FROM t WHERE id = 3) v,
13 TABLE(v.week_order_qties) oq,
14 TABLE(v.week_sale_qties) sq
15 where w.week_date = oq.week_date(+)
16 and w.week_date = sq.week_date(+);
WEEK_DAT ID WEEK_DAT QTY WEEK_DAT QTY
-------- ---------- -------- ---------- -------- ----------
06/10/14 3
13/10/14 3
20/10/14 3
27/10/14 3 27/10/14 9
27/10/14 3 27/10/14 10
03/11/14 3 03/11/14 11
03/11/14 3 03/11/14 12
7 rows selected.
When I try to convert this query to using ANSI syntax, no rows are returned:
SQL>
SQL> with weeks AS (select TO_DATE('06/10/2014', 'dd/mm/yyyy') + 7 * (level-1) week_date
2 from dual
3 connect by level <= 5
4 )
5 select w.week_date,
6 t.id,
7 oq.week_date,
8 oq.qty,
9 sq.week_date,
10 sq.qty
11 FROM weeks w
12 JOIN t ON t.id = 3
13 LEFT JOIN TABLE(t.week_order_qties) oq ON oq.week_date = w.week_date
14 LEFT JOIN TABLE(t.week_sale_qties) sq ON sq.week_date = w.week_date
15 ;
no rows selected
Am I missing something obvious?
Thanks