1: CREATE VIEW [dbo].[v_SomeView] AS
2: SELECT [BH].[Col1],
3: [BH].[Col2],
4: [BH].[Col3],
5: [BH].[Col4],
6: [BH].[Col5],
7: CASE WHEN [BHPP].[OtherCol1] IS NULL THEN -1 ELSE 1 END As [Col6],
8: CASE WHEN [BHPP].[OtherCol1] IS NULL THEN 'Not Applicable' ELSE 'PowerPack' END As [Col7],
9: CASE WHEN [BHPP].[OtherCol1] IS NULL THEN [BH].[Col5] ELSE [BHPP].[OtherCol2] END As [Col8]
10: FROM [dbo].[Table1] as [BH]
11: LEFT JOIN (SELECT [OtherCol1], [OtherCol2], [OtherCol3]
12: FROM [$(CMD)].[dbo].[Table2]
13: WHERE [OtherCol1] <> -1) as [BHPP]
14: ON [BH].[Col2] = [BHPP].[Col2]