question

bbunn avatar image
bbunn asked

Is it possible to compute a value in a view (Part_Final) and also use that calculated value to pull in the part description, in the same view statement?

This is what I'm attempting to do...

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 Part_Final = Part_Record) AS Part_Final_Desc

FROM Orders_Table

viewcalculated column
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

THANKS! Much appreciated

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.