CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Lucas_Santos
Lucas_Santos

Reputation: 4740

How can I do a SELECT with IF clause

How can I do a select that IF San_Cliente.NomeCompleto IS NOT NULL THEN San_Cliente.Excluido = 0 ?

Nowaday this is my SELECT

SELECT  Key.Key_Id, 
        Key.User_Id, 
        Key.From, 
        su.CompleteName, 
        Key.Company_Id, 
        Key.User_Id_Responsible, 
        Key.DateTime, 
        Key.Transaction, 
        Key.Client_Id, 
        Key.DateTimeGet, 
        Key.DateTimeDelivered, 
        Key.Hours, 
        Key.ResponsibleName, 
        Key.PersonalId,
        User.CompleteName AS NomeUsuarioVenda, 
        Client.CompleteName 
FROM    San_Chave 
JOIN    User 
    ON  Key.User_Id_Responsible = User.User_Id 
JOIN    User su 
    ON  Key.User_Id = su.User_Id 
LEFT OUTER JOIN Client
    ON  Key.Client_Id = Client.Client_Id 
WHERE   (Key.Delivered = 0 OR Key.Delivered is NULL) 
    and Key.From = 14
    AND User.Deleted = 0

In my WHERE clause, I need put AND Client.Deleted = 0, but just if Client.CompleteName is not null.

How can I do that ?

Upvotes: 1

Views: 122

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

At the very end, add this condition

AND (San_Cliente.NomeCliente is null OR San_Cliente.Excluido = 0)

So if San_Cliente.NomeCliente IS null, the record passes the WHERE clause.
When it IS NOT NULL, then it checks the additional filter.

To summarize,

  • IF San_Cliente.NomeCompleto IS NOT NULL THEN this condition is added to the WHERE clause: AND San_Cliente.Excluido = 0
  • IF San_Cliente.NomeCompleto IS NULL THEN the condition is NOT added

Upvotes: 3

Phil
Phil

Reputation: 497

Use coalesce

coalesce(San_Cliente,0) = 0

http://msdn.microsoft.com/en-us/library/ms190349.aspx

SELECT San_Chave.Chave_Id, San_Chave.Usuario_Id, San_Chave.De, su.NomeCompleto, 
San_Chave.Credenciada_Id, San_Chave.Usuario_Id_Responsavel, San_Chave.DataHora, 
San_Chave.Transacao, San_Chave.Cliente_Id, San_Chave.DataHoraPegou, 
San_Chave.DataHoraDevolverPrevisao, San_Chave.DataHoraEntregou, 
San_Chave.HorasDevolucao, San_Chave.NomeResponsavel, San_Chave.CpfResponsavel, 
San_Chave.RgResponsavel, San_Chave.TelResponsavel, San_Chave.Tel2Responsavel, 
San_Chave.Endereco, San_Chave.Devolvido, San_Chave.TextoDevolucao, 
San_Usuario.NomeCompleto AS NomeUsuarioVenda, San_Cliente.NomeCompleto 
FROM San_Chave JOIN San_Usuario 
ON San_Chave.Usuario_Id_Responsavel = San_Usuario.Usuario_Id 
JOIN San_Usuario su 
ON San_Chave.Usuario_Id = su.Usuario_Id 
LEFT OUTER JOIN San_Cliente 
ON San_Chave.Cliente_Id = San_Cliente.Cliente_Id 
WHERE (San_Chave.Devolvido = 0 OR San_Chave.Devolvido is NULL) 
and San_Chave.De = 14
AND San_Usuario.Excluido = 0 
AND coalesce(San_Cliente,0) = 0

Upvotes: 1

Tim S.
Tim S.

Reputation: 56586

You want:

(San_Chave.NomeCompleto IS NULL or (San_Cliente.NomeCompleto IS NOT NULL AND San_Cliente.Excluido = 0))

There might be a more concise way to represent this logical situation, but I think this will, regardless, be the clearest.

Upvotes: 1

Related Questions