CODEWITHSUNDEEP
oracle-databaseoracle11g
Bishan
Bishan

Reputation: 15702

Column ambiguously defined Error on Creating a view

I'm trying to create a view with below query.

SELECT the_day, t1, t2
  FROM year_days
    LEFT JOIN (SELECT tea_prodcution_date, SUM(quantity) t1
                  FROM TEA_PRODUCTION
                  WHERE field_name like 'T1'
               GROUP BY tea_prodcution_date) ON the_day = tea_prodcution_date
     LEFT JOIN (SELECT tea_prodcution_date, SUM(quantity) t2
                  FROM TEA_PRODUCTION
                  WHERE field_name like 'T2'
               GROUP BY tea_prodcution_date) ON the_day = tea_prodcution_date
 ORDER BY the_day

year_days is a view created with below query.

SELECT add_months(TRUNC(sysdate - (365), 'YYYY'),to_number(to_char(sysdate,'mm'))) + (level - 1) AS the_day
  FROM dual
CONNECT BY level <=
           to_number(TO_CHAR(last_day(add_months(TRUNC(sysdate, 'YYYY'), 11)),
                             'DDD'))

When i'm trying to execute my first query to create a view, i got below error.

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"
*Cause:    
*Action:
Error at Line: 10 Column: 58

Error occurs from ON the_day = tea_prodcution_date line.

how could i correct this error and create above view ?

Upvotes: 0

Views: 1717

Answers (1)

Bishan
Bishan

Reputation: 15702

I found error with my query. query should correct as below.

SELECT the_day, t1, t2
  FROM year_days
    LEFT JOIN (SELECT tea_prodcution_date, SUM(quantity) t1
                  FROM TEA_PRODUCTION
                  WHERE field_name like 'T1'
               GROUP BY tea_prodcution_date) aa ON the_day = aa.tea_prodcution_date
     LEFT JOIN (SELECT tea_prodcution_date, SUM(quantity) t2
                  FROM TEA_PRODUCTION
                  WHERE field_name like 'T2'
               GROUP BY tea_prodcution_date) bb ON the_day = bb.tea_prodcution_date
 ORDER BY the_day

Upvotes: 1

Related Questions