I’ve faced with this error when I tried to join one table with two different tables, in oracle 8i. Someone told me that this is imposibble to overcome and I should write some function and call the function in select statement. I insisted to solve the problem in one query because I wanted to believe that this should be done in Oracle somehow.  Moral of a fable, I found the solution in Ask Tom ( http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3681127952930 ) and I want to share it.

I’m using the same example,

Query that’s giving the error:

SELECT
TIME_DIM.MONTH,
CUSTOMER_DIM.CUSTOMER_NAME,
SUM(SALES_FACTS.HOURS)
FROM
TIME_DIM,
CUSTOMER_DIM,
SALES_FACTS
WHERE
( SALES_FACTS.CUSTOMER_FK(+)=CUSTOMER_DIM.CUSTOMER_PK )
AND ( TIME_DIM.TIME_PK=SALES_FACTS.TIME_FK(+) )
GROUP BY
TIME_DIM.DAY,
CUSTOMER_DIM.CUSTOMER_NAME

Correct query is:

select ...
  from ( select * from time_dim, customer_dim ) A,
       sales_fact
 where a.customer_pk = sales_fact.customer_fk(+)
   and a.time_pk = sales_fact.time_fk(+)
 group by a.day, a.customer_name