Select distinct with condition

Question

I need help to build a query that select distinct with a condition. For example:

SELECT EMP, COD, VEV, GRU, (SELECT DISTINCT CAST(EMP , CHAR(20) ) + CAST(COD , CHAR(20) ) AS CONCAT FROM HCOV
   WHERE GRU = 212 ) FROM HCOV

In this example I need to concatenate EMP and COD column then remove duplicates if the GRU = 212

But my "Concat" column brings null value instead of concatening.

Please help

Answer 1

I am suprised that your query does not error, because the subquery returns more than one row. Regardless, it does not look like you want a subquery at all. Doesn't this do what you want?

select emp, cod, vev, gru, 
    case when gru = 212 then concat(cast(emp as char(20)), cast(cod as char(20))) end as res 
from hcov