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.
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.
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
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 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.
No, you can't CROSS JOIN with ON clause. CROSS join has no ON clause.
BluShadow wrote: No, you can't CROSS JOIN with ON clause. CROSS join has no ON clause.
BluShadow wrote:
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
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
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.
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
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
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
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.
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