use

if (select TOp 1 UNITE_COUT_MATERIEL from DIM_INTER_MATERIEL)= 'Unité'

or

if exists (select 1 from DIM_INTER_MATERIEL where UNITE_COUT_MATERIEL = 'Unité')

both the above query will work in your case.

sub query should return single value if you are using =operator to compare/assign the result of the query.