Hi, Tom,
I'm wondering about the way Oracle detects ambiguously defined columns.
Lets see following query:
SELECT val
FROM (SELECT 1 AS id, SYSDATE AS val FROM dual) A
JOIN (SELECT 1 AS id, SYSDATE AS val FROM dual) B ON (a.id = b.id)
If we try to run it, we get an error:
ORA-00918: column ambiguously defined
Why ORA-00918 does not happens on this query:
SELECT change_date change_date, date_from date_from, date_to date_to, cgp_id
FROM (SELECT 1 ctr_id,
to_date('20070101', 'yyyymmdd') change_date,
to_date('20071106', 'yyyymmdd') date_from,
1 cgp_id,
NULL date_to
FROM dual) cgp_new
JOIN (SELECT 1 id, 'C' code FROM dual) cgp ON (cgp_new.cgp_id = cgp.id)
JOIN (SELECT 1 ctr_id,
to_date('20070201', 'yyyymmdd') change_date,
to_date('20071107', 'yyyymmdd') date_from,
2 cgp_id,
to_date('20071105235959', 'yyyymmddhh24miss') date_to
FROM dual) cgp_old ON (cgp_old.ctr_id = cgp_new.ctr_id AND
cgp_old.date_to = cgp_new.date_from - 1 / (24 * 60 * 60))
JOIN (SELECT 2 id, 'D' code FROM dual) cgp2 ON (cgp2.id = cgp_old.cgp_id);
Thanks,
Andris
It is a bug that the second one worked.
Fixed in 11g:
ops$tkyte%ORA11GR1> SELECT change_date change_date, date_from date_from, date_to date_to, cgp_id
2 FROM (SELECT 1 ctr_id,
3 to_date('20070101', 'yyyymmdd') change_date,
4 to_date('20071106', 'yyyymmdd') date_from,
5 1 cgp_id,
6 NULL date_to
7 FROM dual) cgp_new
8 JOIN (SELECT 1 id, 'C' code FROM dual) cgp ON (cgp_new.cgp_id = cgp.id)
9 JOIN (SELECT 1 ctr_id,
10 to_date('20070201', 'yyyymmdd') change_date,
11 to_date('20071107', 'yyyymmdd') date_from,
12 2 cgp_id,
13 to_date('20071105235959', 'yyyymmddhh24miss') date_to
14 FROM dual) cgp_old ON (cgp_old.ctr_id = cgp_new.ctr_id AND
15 cgp_old.date_to = cgp_new.date_from - 1 / (24 * 60 * 60))
16 JOIN (SELECT 2 id, 'D' code FROM dual) cgp2 ON (cgp2.id = cgp_old.cgp_id);
SELECT change_date change_date, date_from date_from, date_to date_to, cgp_id
*
ERROR at line 1:
ORA-00918: column ambiguously defined