Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Outer Join with the TABLE operator

GregVDec 11 2014 — edited Jan 19 2015

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

Comments

BluShadow

Well, syntactically it looks equivalent.

It's likely one of the ANSI implementation bugs that exist in Oracle where it's re-writing the query badly.

Perhaps check the trace etc. to see how the query has been rewritten.

chris227

GregV wrote:

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.

12       (SELECT * FROM t WHERE id = 3) v,


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

Just from reading it:

Where you had a cartesian product you now have a join.

What if you change it to

12  CROSS JOIN (select * from t where t.id = 3) v


or perhaps just


CROSS JOIN ON t.id = 3


I am not quite sure if this is valid syntax, cross join with on-clause.

BluShadow

No, you can't CROSS JOIN with ON clause.  CROSS join has no ON clause.

chris227

BluShadow wrote:

No, you can't CROSS JOIN with ON clause.  CROSS join has no ON clause.

Ok, was to lazy to consult the docs, but was pretty sure that it is wrong therefore the first approach with the inline view.

After a deeper look i come up with this:

with weeks AS (select TO_DATE('06/10/2014', 'dd/mm/yyyy') + 7 * (level-1) week_date

                from dual

                connect by level <= 5

                )

select w.week_date,

        oq.id,

        oq.week_date,

        oq.qty,

        sq.week_date,

        sq.qty

FROM weeks w

left outer join (select t.id, oq.week_date, oq.qty from t, TABLE(t.week_order_qties) oq where t.id = 3) oq

ON (w.week_date = oq.week_date)

left outer join (select t.id, sq.week_date, sq.qty from t, TABLE(t.week_sale_qties ) sq where t.id = 3) sq

oN (w.week_date = sq.week_date)

order by w.week_date

WEEK_DATEIDWEEK_DATEQTYWEEK_DATEQTY
10/06/2014 - - - - -
10/13/2014 - - - - -
10/20/2014 - - - - -
10/27/2014 3 10/27/2014 10 - -
10/27/2014 3 10/27/2014 9 - -
11/03/2014 - - - 11/03/2014 11
11/03/2014 - - - 11/03/2014 12
GregV

Thanks Chris and Blu for looking at this.

Chris, it works, but this is twisted. It mixes old style and ANSI syntax, so I'd better stick to the old style only.

In fact, I'm still one of those guys who writes queries with the old style syntax. I do resort to ANSI syntax when necessary. As Blu said, this is probably a bug with the ANSI syntax, but I haven't found any note in MOS about it (searching the KB with keywords such as "TABLE" is no great help ).

Anyway, I'll open an SR and see where it goes. I'll keep in touch.

chris227

GregV wrote:

Thanks Chris and Blu for looking at this.

Chris, it works, but this is twisted. It mixes old style and ANSI syntax, so I'd better stick to the old style only.

Upps, you were right

We may just change that two lines to be pure:

with weeks AS (select TO_DATE('06/10/2014', 'dd/mm/yyyy') + 7 * (level-1) week_date

                from dual

                connect by level <= 5

                )

select w.week_date,

        oq.id,

        oq.week_date,

        oq.qty,

        sq.week_date,

        sq.qty

FROM weeks w

left outer join (select t.id, oq.week_date, oq.qty from t join TABLE(t.week_order_qties) oq on t.id = 3) oq

ON (w.week_date = oq.week_date)

left outer join (select t.id, sq.week_date, sq.qty from t join TABLE(t.week_sale_qties ) sq on t.id = 3) sq

oN (w.week_date = sq.week_date)

order by w.week_date

However, i played around with this query and there are some results i cant explain. So i would be interested in the outcome of the SR. May be it has something do to with the internal correlation of the nested tables to there parent rows which may be corrupted in this straight forward transformed ansi style notation.

GregV

Hi guys,


Just to let you know that after a lot of beating around the bushes with the Oracle support, a bug has finally been filed for this problem:

Bug 20363558 : WRONG RESULTS FOR ANSI JOIN ON NESTED TABLE

1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 16 2015
Added on Dec 11 2014
7 comments
303 views