CODEWITHSUNDEEP
sqloracle-database
John Wick
John Wick

Reputation: 745

Column Ambiguously Defined - No Line / Column Number

I've been trying to create a query that joins two sets of tables together using several columns. I have tried assigning aliases to each column but I am still receiving a (ORA-00918: column ambiguously defined) error with no line number / direction on which column oracle has an issue with. Can someone please help me? Been stuck on this issue for hours now. My code is below, thanks in advance!

 SELECT *
 FROM
 (select v.value_id as i_value_id
 , v.value_nb as i_value_nb
 , v.utc_offset as i_utc_offset
 , v.data_Date as i_data_date
 , v.hr_utc as i_hr_utc
 , v.utc_offset as i_utc_offset
 , v.hr as i_hr
 , v.hr_num as i_hr_num
 , v.data_Code as i_data_code
 , v.Code as i_code
 , ff.form_Field_tx as i_form_field_tx
 , sv.submission_value_id as i_submission_value_id
 , s.submission_name_tx as i_submission_name_tx
 , s.submission_id as i_submission_id
 , fl.form_line_tx as i_form_line_tx
 , fs.form_section_tx as i_form_section_tx
 , sf.form_name_tx as i_form_name_tx
 , sf.form_label_tx as i_form_label_tx
 , sf.form_number_tx as i_form_number_tx
 , sf.survey_id as i_survey_id
   from value v
   join sub_value sv on v.value_id = sv.value_id
   join form_Field ff on sv.form_Field_id = ff.form_Field_id
   join sub s on sv.submission_id = s.submission_id
   LEFT OUTER JOIN form_line fl ON ff.form_line_id = fl.form_line_id
   LEFT OUTER JOIN form_section fs ON fl.form_section_id = fs.form_section_id
   LEFT OUTER JOIN survey_form sf ON fs.survey_form_id = sf.survey_form_id) subq1
   JOIN
   (select va.value_id as o_value_Id
   , va.value_nb as o_value_nb
   , va.utc_offset as o_utc_offset
   , va.data_Date as o_data_date
   , va.hr_utc as o_hr_utc
   , va.hr as o_hr
   , va.hr_num as o_hr_num
   , va.data_Code as o_data_Code
   , va.balancing_Authority_Code as o_balancing_authority_code
   , ff2.form_Field_tx as o_form_field_tx
   , sva.submission_value_id as o_submission_value_id
   , s.submission_id as o_submission_id
   , fl.form_line_tx as o_form_line_tx
   , fs.form_section_tx as o_form_section_tx
   , sf.form_name_tx as o_form_name_tx
   , sf.form_label_tx as o_form_label_tx
   , sf.form_number_tx as o_form_number_tx
   , sf.survey_id as o_survey_id
   from submission s
   join submission_value_audit sva on s.submission_id = sva.submission_id
   join value_audit va on sva.value_id = va.value_id
   join form_Field ff2 on sva.form_Field_id = ff2.form_Field_id
   LEFT OUTER JOIN form_line fl ON ff2.form_line_id = fl.form_line_id
   LEFT OUTER JOIN form_section fs ON fl.form_section_id = fs.form_section_id
   LEFT OUTER JOIN survey_form sf ON fs.survey_form_id = sf.survey_form_id) subq2
     on subq1.sub_id = subq2.sub_id
     on subq1.code = subq2.code;

Upvotes: 0

Views: 600

Answers (2)

Niharika Bitra
Niharika Bitra

Reputation: 477

ORA-00918: column ambiguously defined: You tried to execute a SQL statement that joined two or more tables, where a column with the same name exists in both tables.

Reference: https://www.techonthenet.com/oracle/errors/ora00918.php

The duplicate column name is below.

SELECT *
FROM
(
  select ...
     , v.utc_offset as i_utc_offset
     ...
     , v.utc_offset as i_utc_offset

Upvotes: 1

Shaun Peterson
Shaun Peterson

Reputation: 1790

looks like you have a duplicate definition of i_utc_offset (see code snippet below)

(select v.value_id as i_value_id
 , v.value_nb as i_value_nb
 , v.utc_offset as i_utc_offset
 , v.data_Date as i_data_date
 , v.hr_utc as i_hr_utc
 , v.utc_offset as i_utc_offset

Upvotes: 2

Related Questions