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

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

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;

Solution

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;