[SOLVED] Oracle - Replace null values for pivot columns with join from another table

Oracle - Replace null values for pivot columns with join from another table

I've a pivot table output and now I want to check for the values from the pivot columns and replace the values if null from another column from another table.

Invoice_No	Column	value
111	A	One
111	B	Two
111	C	Three
111	E	Five

(SELECT Invoice_No, new_value, Column_Name  FROM table_name)
PIVOT(max(new_value) 
FOR Column_Name IN ('A','B','C','D','E'))

This returned the following table

Invoice_No	'A'	'B'	'C'	'D'	'E'
111	One	Two	Three	null	Five

Now, I want to replace the null value from column D with a value from another table that matches the Invoice_no.

with temp as
(SELECT Invoice_No, new_value, Column_Name  FROM table_name)
PIVOT(max(new_value) 
FOR Column_Name IN ('A','B','C','D','E'))
select nvl(temp.D,bckup.D)
from
(select A,B,C,D,E from Backup_table) bckup
join
temp
on
temp.Invoice_No = bckup = Invoice_No

Now, I'm getting the error saying D Column does not exist.

Solution

Pivot will rename your column as 'D' not D only. So, You need a simple update in your query as -

WITH temp AS(SELECT *
               FROM(SELECT Invoice_No, new_value, Column_Name
                      FROM table_name)
              PIVOT(max(new_value) FOR Column_Name IN ('A' AS A,'B' AS B,'C' AS C,'D' AS D,'E' AS E)
            )
SELECT NVL(temp.D,bckup.D)
  FROM(SELECT A,B,C,D,E
         FROM Backup_table) bckup
  JOIN temp ON temp.Invoice_No = bckup.Invoice_No