ORA-00918: column ambiguously defined & ORA-00904 Invalid Identifier on SELECT query with Pivot

Question

I'm fairly new to SQL, so forgive me if I'm not using correct terminology or missing something simple!

I'm trying to create an output file with RC_ID on the left, and cost types across the top with cost amounts summed for each RC_ID/ cost type combination. I've tried doing this using subquery and also using WITH to create a CTE to reference. I get the same errors using either method.

When I use Select *, I get "ORA-00918: column ambiguously defined," even with naming all columns. When I instead try to list the specific fields to return, I get ORA-00904: invalid identifier errors.

Any help would be appreciated!

I'm trying for something like this (with more columns for all the cost types listed in my query, and eventually more RC rows.

enter image description here

This SQL returns error "ORA-00918: column ambiguously defined." on the row "Select *"

WITH TABLE1 AS (
SELECT RC_ID, COST_TYPE AS COST_TYPE, SUM(AMOUNT) AS COST_AMOUNT, SUM(REC_AMT) AS COST_RELEASED
FROM REDACTED.RC_LN_COST_V
WHERE RC_ID = 1837161
GROUP BY RC_ID, COST_TYPE)

SELECT *
FROM TABLE1
PIVOT (SUM(COST_AMOUNT),SUM(COST_RELEASED) FOR COST_TYPE IN ('Commissions','Commissions Adjustment','Commissions Adjustment2','Fringe Commissions','Fringe Cost Adjustment','Fringe Cost Adjustment2','Install Costs Estimate',
'3rd Party License Costs','3rd Party Software Usage TERMLIC Costs'))
ORDER BY RC_ID;

When I try entering the actual columns to return from TABLE1, I then get ORA-00904: "COST_RELEASED": invalid identifier on the 2nd Select statement.
Note- that same error occurs for COST_NAME and COST_AMOUNT.

WITH TABLE1 AS (
SELECT RC_ID, COST_TYPE AS COST_TYPE, SUM(AMOUNT) AS COST_AMOUNT, SUM(REC_AMT) AS COST_RELEASED
FROM REDACTED.RC_LN_COST_V
WHERE RC_ID = 1837161
GROUP BY RC_ID, COST_TYPE)

SELECT RC, COST_TYPE, COST_AMOUNT, COST_RELEASED
FROM TABLE1
PIVOT (SUM(COST_AMOUNT),SUM(COST_RELEASED) FOR COST_TYPE IN ('Commissions','Commissions Adjustment','Commissions Adjustment2','Fringe Commissions','Fringe Cost Adjustment','Fringe Cost Adjustment2','Install Costs Estimate',
'3rd Party License Costs','3rd Party Software Usage TERMLIC Costs'))
ORDER BY RC_ID;

Answer 1

as you have to values you need to alias them so that they are not ambiguos

WITH TABLE1 AS (
SELECT RC_ID, COST_TYPE AS COST_TYPE, SUM(AMOUNT) AS COST_AMOUNT, SUM(REC_AMT) AS COST_RELEASED
FROM REDACTED.RC_LN_COST_V
WHERE RC_ID = 1837161
GROUP BY RC_ID, COST_TYPE)
SELECT *
FROM TABLE1
PIVOT (SUM(COST_AMOUNT) AS SUM1,SUM(COST_RELEASED) AS SUM2 FOR COST_TYPE IN ('Commissions','Commissions Adjustment','Commissions Adjustment2','Fringe Commissions','Fringe Cost Adjustment','Fringe Cost Adjustment2','Install Costs Estimate',
'3rd Party License Costs','3rd Party Software Usage TERMLIC Costs'))
ORDER BY RC_ID;

Answer 2

Expanding a bit on @nbk's answer, if you simplify your current query so that you only have one sum and one pivot value:

PIVOT (SUM(COST_AMOUNT) FOR COST_TYPE IN ('Commissions'))

then you will see that the result set ends up with a column called 'Commissions' (with the quotes and case). If you get the other sum instead:

PIVOT (SUM(COST_RELEASED) FOR COST_TYPE IN ('Commissions'))

... then you get exactly the same column name in the result set. So if you do both:

PIVOT (SUM(COST_AMOUNT), SUM(COST_RELEASED) FOR COST_TYPE IN ('Commissions'))

... then you get two result columns with the same name, which is where the ORA-00918 error appears. As @nbk said, if you alias the aggregates:

PIVOT (SUM(COST_AMOUNT) AS SUM1,SUM(COST_RELEASED) AS SUM2 FOR COST_TYPE IN ('Commissions'))

... that ambiguity is resolved as the column names are now 'Commissions'_SUM1 and 'Commissions'_SUM2, so those are unique. Ugly, but unique.

There is a further problem if you are on an older version of Oracle that only supports identifier lengths of 30 characters (or bytes). Some of your pivot values exceed that length; for example '3rd Party Software Usage TERMLIC Costs' would generate a column name '3rd Party Software Usage TERMLIC Costs'_SUM1 which is 45 characters. With the 30-character limit those identifier names will be truncated, so both '3rd Party Software Usage TERMLIC Costs'_SUM1 and '3rd Party Software Usage TERMLIC Costs'_SUM2 become '3rd Party Software Usage TERM, and you're back to ambiguous names and ORA-00918.

You can avoid that possibility, and also make the column names easier to work with (as they won't have to be treated as quoted identifiers), by aliasing the values as well, for example:

PIVOT (
  SUM(COST_AMOUNT) AS amt, SUM(COST_RELEASED) AS rel
  FOR COST_TYPE IN (
    'Commissions' AS comm,
    'Commissions Adjustment' AS comm_adj,
    'Commissions Adjustment2' AS comm_adj2,
    'Fringe Commissions' AS fringe_comm,
    'Fringe Cost Adjustment' AS fringe_comm_adj,
    'Fringe Cost Adjustment2' AS fring_comm_adj2,
    'Install Costs Estimate' AS inst_cost_est,
    '3rd Party License Costs' AS tp_lic_cost,
    '3rd Party Software Usage TERMLIC Costs' AS tp_su_term_lic_cost
  )
)

to get columns names COMM_AMT, COMM_REL, COMM_ADJ_AMT, ... , TP_SU_TERM_LIC_COST_AMT and TP_SU_TERM_LIC_COST_REL.

You also don't need the CTE, so you can simplify to:

SELECT *
FROM RC_LN_COST_V
PIVOT (
  SUM(AMOUNT) AS amt, SUM(REC_AMT) AS rel
  FOR COST_TYPE IN (
    'Commissions' AS comm,
    'Commissions Adjustment' AS comm_adj,
    'Commissions Adjustment2' AS comm_adj2,
    'Fringe Commissions' AS fringe_comm,
    'Fringe Cost Adjustment' AS fringe_comm_adj,
    'Fringe Cost Adjustment2' AS fring_comm_adj2,
    'Install Costs Estimate' AS inst_cost_est,
    '3rd Party License Costs' AS tp_lic_cost,
    '3rd Party Software Usage TERMLIC Costs' AS tp_su_term_lic_cost
  )
)
WHERE RC_ID = 1837161
ORDER BY RC_ID;

fiddle in 21c, and the same thing in 11g showing it still works there with all the aliases applied. (The third-from-last query, without the value aliases, still gets ORA-00918 in 11g because of the 30-byte limit.)

You can abbreviate them however makes sense to you, of course. And you can assign your own column aliases to the generated names in the outer select list - instead of SELECT * you can do SELECT RC_ID, COMM_AMT AS "Commisions COST_AMOUNT", ... - though you're still restricted to 30-byte aliases in older versions, and that kind of aliasing, particularly with quoted identifiers, often belongs in a display/reporting layer.

---

When I try entering the actual columns to return from TABLE1, I then get ORA-00904: "COST_RELEASED": invalid identifier

That's because those column names do not exist in the projection after the pivot. They are 'consumed' by the pivot operation because they are referenced in the aggregate functions and in the FOR clause. The only original column that remains in the projection it RC_ID, because that wasn't part of the pivot clause. Effectively the pivot aggregates are grouped by the non-pivot columns.