Hi all, to day i had some very strange situation with our DB. I try to reproduce same error on hr schema but i was not able to do that so i am asking you does any one know why oracle did not show me error and it should.
Version of DB is
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
And query was something like this
(you dont need to know what query do)
select * from (
SELECT p.dep_id
, f.customer_id customer_id
, p.full_name full_name
, pt.payments_type_id
, sum(nvl(f.dept, 0)) - sum(nvl(u.upl, 0)) + sum(nvl(f.wanted, 0)) saldo
FROM order_table f
JOIN customer_table p
ON f.customer_id = p.customer_id
LEFT OUTER JOIN s_departmente srj
ON p.dep_id = srj.dep_id
LEFT OUTER JOIN (SELECT customer_id
, payments_id
, nvl(sum(amaount),0) upl
FROM payments r
WHERE trunc(r.date_k) <= to_date('31.12.2013', 'dd.MM.yyyy')
GROUP BY customer_id,payments_id) u
ON f.customer_id = u.customer_id
AND f.payments_id = u.payments_id
LEFT OUTER JOIN payments_type pt
ON f.payments_type_id = pt.payments_type_id
LEFT OUTER JOIN payments_cateogry pc
ON CASE f.payments_cateogry_id
WHEN 35
THEN 34
ELSE f.payments_cateogry_id
END = pc.payments_cateogry_id
WHERE trunc(date_v) <= nvl((to_date('31.12.2012', 'dd.MM.yyyy')),to_date('31.12.9999', 'dd.MM.yyyy'))
AND trunc(date_k) <= to_date('31.12.2013', 'dd.MM.yyyy')
AND nvl(f.dept,0)<>nvl(u.upl,0)+nvl(f.wanted,0)
AND customer_id = 49929542
GROUP BY f.customer_id
, p.full_name
, pt.payments_type_id
, p.dep_id
, p.customer_id);
This query should throw exception ORA-00918: column ambiguously defined (on my bold / italic line ), but it did not. So i was thinking this was somehow SQL Developer bug, but we try it on TOAD and query execute fine with no exception.
First time when we run query we got like 112 rows second time we run query we got 3 rows. When you put table alias p.customer_id = 49929542 it runs fine.
So, simple question is why oracle allowed me to run query in first place and is there something i am missing, some spacial case when oracle ignore this, something with optimazer maybe?!
Sorry if my Eng. is not best one and thank you for your time.
All best.