Select from column B if the field selected in column A is null

Question

Select from column B if the field selected in column A is null

Navigation

#1 by (1 votes)

0

In a SQL query I was trying to work out a way to select a value from another B column if the selected A column is null . At first I made the following query:

SELECT COALESCE(NULLIF(max(convert(smalldatetime,DEP.DATAENTRADA,100)),null), DEP.DATANASCIMENTO,100) as UltimoDependente
    from DEPENDENTES DEP
        join FUNCIONARIOS FUNC  on FUNC.FILIAL=DEP.FILIAL and FUNC.MATRICULA=DEP.MATRICULA and FUNC.D_E_L_E_T_ = ' ' 
     where FUNC.MATRICULA = '10'
       and FUNC.FILIAL = '01'
       and DEP.R_E_C_D_E_L_=0

And the query does not work. Is there any way to accomplish this?

sql sql-server

asked by anonymous 25.10.2018 / 20:01

1 answer

compare sql tables displaying different result Tanned system with Stored Procedure

score 1 · Accepted Answer

Do this:

select
  COALESCE(DEP.DATAENTRADA, DEP.DATANASCIMENTO) as UltimoDependente
from
  DEPENDENTES DEP
  join FUNCIONARIOS FUNC on FUNC.FILIAL = DEP.FILIAL
  and FUNC.MATRICULA = DEP.MATRICULA
  and FUNC.D_E_L_E_T_ = ' '
where
  FUNC.MATRICULA = '10'
  and FUNC.FILIAL = '01'
  and DEP.R_E_C_D_E_L_ = 0

The function COALESCE returns the first non-null argument, so if the value of the DEP.DATAENTRADA field is not null, it will be returned, if it is null, and the value of the DEP.DATANASCIMENTO field is not null , the second is returned. If both are null, the return of this field will be NULL .

Reference:

COALESCE (Transact-SQL) | Microsoft Docs