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

Asked

Viewed 134 times

0

In a consultation SQL was trying to work out a way to select a value from another column B if the selected column value To is null. I first 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 doesn’t work. Is there any way to do this?

sql sql-server

  • You want to return the highest value of the field DEP.DATAENTRADA, and if this field is null you want to return the field value DEP.DATANASCIMENTO, is that it? They have different types?

  • no. I want to return the last date. If the DATAINPUT field is not null, I use it to make the query. Now if the field does not have a registered date, ie null, I want to use the field DATANASCIMENTO

  • 1

    Have you tried using Coalesce alone? Like this: SELECT COALESCE(SUA_DATA_ENTRADA, DATANASCIMENTO)

  • What mistake it makes?

1 answer

1


Do so:

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 argument that is not null, then if the field value DEP.DATAENTRADA is not null, it will be returned, if null, and the value of the field DEP.DATANASCIMENTO is not null, the second will be returned. If both are null the return of this field will be NULL.

Reference:

COALESCE (Transact-SQL) | Microsoft Docs

  • Thanks for the reply Peter... I made a small change in line COALESCE(isnull(max(convert(smalldatetime,SRB.RB_DTENTRA,100)),''), isnull(max(convert(smalldatetime,SRB.RB_DTNASC,100)),'')) as UltimoDependente to work with date. However, by default SQL Server sets the date 01-01-1900 for fields datetime null. That way the field of the first column (DATADELIVERY) is null, it will always be returned that date. Because of this I used the MAX(), to get the most value. however it did not work

  • 1

    I think you’re confusing, if you assign the value NULL to the field it will be null, regardless of the type (as long as it allows nulls, of course). I just did a test, I created a test table with an id field, another one like smalldatetime, another datetime and another datetime2, and all were null value when creating a new record and did not report anything in these fields.

  • But, if your date field can have the value 01-01-1900 to demonstrate that it has no value, you could use the expression CASE or the function IIF (if the version of your SQL Server is larger or equal to 2012): https://answall.com/a/336945/86952, something like: COALESCE( IIF(DEP.DATAINPUT = '01-01-1900', NULL, DEP.DATAINPUT), DEP.DATABIRTH)

  • I really confused Pedro kkk, in case, if the field is empty and use the convert for datetime, by default SQL uses the date 1990-01-01

  • Tranquil, it happens! : ) But if the answer has solved the original question, mark it as accepted (by clicking on the "visa sign", just below the down arrow), please, to help others who might have the same question. Look at these pages: What should I do if someone answers my question?, and Why it is important to vote?.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.