question

Few different approaches to this.

create or alter view Parts_View
as
select
 Part, 
 Part_Corrected, 
 case
  when Part_Corrected is not null then Part_Corrected
  else Part 
 end as Part_Final,
(select Part_Desc from  Part_Table where case when Orders_Table.Part_Corrected is not null then Orders_Table.Part_Corrected else Orders_Table.Part end = Part_Record) as Part_Final_Desc
from  Orders_Table;




create or alter view Parts_View
as
select
 Part, 
 Part_Corrected, 
 case
  when Part_Corrected is not null then Part_Corrected
  else Part 
 end as Part_Final,
 Part_Table.Part_Desc as Part_Final_Desc
from  Orders_Table
join Part_Table on Part_Table.Part_Record = case when Orders_Table.Part_Corrected is not null then Orders_Table.Part_Corrected else Orders_Table.Part end;




create or alter view Parts_View
as
select
 Part, 
 Part_Corrected, 
 isnull(Part_Corrected, Part) as Part_Final,
(select Part_Desc from  Part_Table where isnull(Orders_Table.Part_Corrected, Orders_Table.Part) = Part_Record) as Part_Final_Desc
from  Orders_Table;






create or alter view Parts_View
as
select
 Part, 
 Part_Corrected, 
 isnull(Part_Corrected, Part) as Part_Final,
 Part_Table.Part_Desc as Part_Final_Desc
from  Orders_Table
join Part_Table on Part_Table.Part_Record = isnull(Orders_Table.Part_Corrected, Orders_Table.Part);

Will depend on the data as to if these give the correct results and/or performance.