Consider:

select distinct
    a.id, a.number, b.number as school_number, b.id as school_id
from
    (select distinct a.id, a.number
     from faculty a
     where a.status = 'active') as t1
full outer join
    (select distinct b.id, b.number
     from staff b
     where b.status = 'active') as t2 on t1.id = t2.id

I wanted to use the above code inside multiple when case statements in SQL Server. How can I do this?

For that I have created a view:

create view v_data
as
    (select
         name_loc,
         case
             when name_loc like '%city%' or
             when name_loc like '%cities%'
                 then 'Survey'

Now I am stuck how to use the first code inside the case statement which is present in the virtual table, i.e., in the view.

I get an error trying to create the view:
Subquery returned more than 1 value.this is not permitted when the subquery follows =,!=,<=,>=,> Or when the subquery is used as an expression