ORA-00918: column ambiguously defined when using pivot

Question

Can someone please take a look at my query. when i try to un-comment any of the commented fields, the oracle throws the error.

SELECT * 
FROM   ( 
              SELECT ratfac.rating_id, 
                     ratfac.label_en, 
                     ratfac.ratingmodel_factor_id, 
                     ratfac.weight, 
                     ratfac.rating_calculated, 
                     ratfac.rating, 
                     ratfac.rating_adjusted, 
                     ratfac.override_comment, 
                     ratfac.rating_override, 
                     ratfac.notch_value, 
                     ratfac.notch_value_calculated, 
                     ratfac.notch_value_adjusted, 
                     ratfac.score, 
                     ratfac.score_calculated, 
                     ratfac.score_adjusted, 
                     ratfac.factor_comment 
              FROM   vw_ratingfactor ratfac ) pivot ( min(ratingmodel_factor_id) ratingmodel_factor_id, min(weight) weight, min(rating_calculated) rating_calculated, min(rating) rating,
       --MAX(RATING_ADJUSTED) as RATING_ADJ, 
       min(override_comment) override_comment, min(rating_override) rating_override, min(notch_value) notch_value,
       --MIN(NOTCH_VALUE_CALCULATED) NOTCH_VALUE_CALCULATED, 
       --MIN(NOTCH_VALUE_ADJUSTED) NOTCH_VALUE_ADJUSTED, 
       min(score) score, min(score_calculated) score_calculated, min(score_adjusted) score_adjusted, min(factor_comment) factor_comment FOR label_en IN ('Market'
                                                                                                                                                                || chr(38)
                                                                                                                                                                ||'Competitiveness'        AS marketcompetitiveness,
                                                                                                                                                         'Industry'                        AS industry,
                                                                                                                                                         'Company Strategy and Management' AS company_stratergy_mgmt,
                                                                                                                                                         'Financial Performance'           AS financial_performance,
                                                                                                                                                         'Need for and Access to Funds'    AS needforfunds ) )

Answer 1

The problem is the length of the generated column names for the pivoted columns.

The longest prefix you have is company_stratergy_mgmt, which is 22 characters. An underscore is automatically added before the aggregate alias, so you only have 7 characters left before you hit the 30-character column name limit. Oracle is silently truncating the generated name at that 30-char limit, and that is causing the duplicates.

Some of the aggregates are OK, e.g. rating, which generates company_stratergy_mgmt_rating - 29 characters. You can then have one of the names like rating_%. But you have two, so it tries to generate names like:

company_stratergy_mgmt_rating_calculated  -> company_stratergy_mgmt_rating_
company_stratergy_mgmt_rating             OK
company_stratergy_mgmt_rating_override    -> company_stratergy_mgmt_rating_

Both company_stratergy_mgmt_rating_calculated and company_stratergy_mgmt_rating_override are truncated to the same 30-character name, company_stratergy_mgmt_rating_. And as they are the same, the column name is a duplicate, hence the ORA-00918 error.

Others have the same problem, these are just examples. If you fixed the spelling of company_stratergy to lose the extra r you'd get away with it with those columns, and even if you uncommented the rating_adjustment aggregate, as they would then end with _a, _c and _o and be unique again. But you would still get the same problem if you uncommented the notch_value_% aggregates.

So you need to choose shorter prefixes, or shorter aggregates aliases, so that all of the generated names are preferably not truncated, and if they are truncated then they are still unique.