SELECT DISTINCT ABC, DEF, ... 
FROM view_Table Table
LEFT JOIN ( SELECT A, CASE WHEN B IS NULL THEN B ELSE B + C + ' ' END as Foo2,  FROM view_Table2 
LEFT JOIN view_Table3 Table3 ON ID = Table3.ID 
LEFT JOIN view_Table4 Table4 ON ID = Table4.ID 
LEFT JOIN view_Table5 Table5 ON ID = Table5.ID ) AS tmp ON ID = tmp.ID 
LEFT JOIN view_table6 Table6 ON 1 = 2 
LEFT JOIN view_table7 Table7 ON 1 = 2 
INNER JOIN view_Table8 Table8 ON ID = Table8.ID
LEFT JOIN view_Table9 Table9 ON ID = Table9.ID
WHERE XY IS NULL AND [NNN] is not null 
AND XYZ >= CAST (1 as int ) AND MMM <= CAST (2 as int )