Case in Where using SQL Server

Asked

Viewed 235 times

1

I have the following code that lists certain trade agreements. You have the Yes or No option.

   DECLARE @Opcao VARCHAR(7)

   SET @Opcao = 'S'

   SELECT * FROM 
   BLA BLA BLA

WHERE
    ((('{Opcao}' = 'S') AND NFDataEmis BETWEEN lme.LogMovEstqDataHora AND ppme.ParcPagMovEstqDataVenc) or ('{Opcao}' = 'N'))
    AND ec.CategCodEstr like '{Categoria}' + '%'
    AND crd.ClasseRecDespCodEstr like '{Acordo}' +'%'
    AND me.MovEstqDataMovimento BETWEEN @Data_inicial AND @Data_final

What happens is that it brings me all the options that are listed the S and the N. I wanted to make a CASE to bring the options that are not in S and N. How could I do that?

SQL SERVER USAGE

sql-server case

  • I don’t quite understand it, but Opcao NOT IN ('S', 'N') or '{Opcao}' <> 'S' doesn’t solve?

  • If the @Option variable is empty, it cannot execute (('{Option}' = ’S') AND Nfdataemis.... If it is S or N it executes. I tried with CASE but it didn’t work. As if it would be with IN?

  • Oh yes, I get it, Option is a column of some table?

  • No Option is a variable that will list the records by Date. If it is S it lists according to one category if N for another. But I want to list them all. So if it is by all it does not execute this line: ((('{Option}' = ’S') AND Nfdataemis BETWEEN lme.Logmovestqdatahora AND ppme.Parcpagmovestqdatavenc) or ('{Opcao}' = 'N'))

  • I’m sorry. I don’t know if you’re slow. but it’s not clear to me yet, so it looks like the variables in {} comes from some code. but I found this response in the OS I might be able to help you

  • Really, the explanation is a bit confusing. But I think the final solution to our exact understanding may not be the issue at this point. Try to take a look at the answers and if they don’t fit, try to explain why they don’t work

Show 1 more comment

2 answers

2


If the @Option variable is empty, it cannot execute (('{Option}' = ’S') AND Nfdataemis.... If it is S or N it executes (...)
Option is a variable that will list the records by Date. If it is S it lists according to one category if N by another. But I want to list all of them. So if it’s for all of them he doesn’t run that line:
((('{Opcao}' = 'S') AND NFDataEmis BETWEEN lme.LogMovEstqDataHora AND ppme.ParcPagMovEstqDataVenc) or ('{Opcao}' = 'N'))

The description of the problem seems to me not very clear, aside from the mixture of {Opcao} and @Opcao in the same code. Considering the description of the problem and the subsequent comments of the author of the topic, here is the outline of the code.

-- código #1
SELECT ...
  from ...
  where (@Opção is NULL 
         or (@Opção = 'S' 
             and NFDataEmis between lme.LogMovEstqDataHora and ppme.ParcPagMovEstqDataVenc)
         or @Opção = 'N')
        and ...

1

Include the option on WHERE

WHERE
        (IsNull(@opcao,'') In ('S') And NFDataEmis Between lme.LogMovEstqDataHora And ppme.ParcPagMovEstqDataVenc)
        Or
        (IsNull(@opcao,'') In ('N') And ec.CategCodEstr Like '{Categoria}' + '%' AND crd.ClasseRecDespCodEstr like '{Acordo}' +'%')

Browser other questions tagged sql-server case

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