Conditional help in SQL WHERE

Question

Come on, guys, I’m putting together an SQL query. In this query, the field SD1010.D1_TES can assume N values, but if this field is equal to empty, I need one more restriction to be applied (SF4010.F4_TEXTO NOT LIKE '%*%'). For this, first tested use conditionals with CASE and also with if, follows example below:

WHERE
.
.
. AND
(
CASE
    WHEN SD1010.D1_TES = '   ' THEN (SF4010.F4_TEXTO NOT LIKE '%*%')
)

It turns out that SQL points error, already tried to put parentheses, take parentheses, use if Lse and even then the error happens.

The only way I could was to duplicate the restrictions using OR and only add the restriction of D1_TES and F4_TEXTO:

WHERE
(
**SD1010.D1_TES = '   ' AND**
-- RETIRA DELETADOS
(SD1010.D_E_L_E_T_<>'*') AND 
(   
    --CLASSIFICADO E ATUALIZOU ESTOQUE
    SF4010.F4_ESTOQUE = 'S' OR
    --PENDENTE DE CLASSIFICACAO PARA ESTOQUE
    (SD1010.D1_CC='' AND SD1010.D1_TES='') 
) AND
SF1010.F1_DTDIGIT>=concat(YEAR(GETDATE()),'0',month(GETDATE())-1,'01') AND -- DADOS INSERIDOS A PARTIR DO PRIMEIRO DIA DO MÊS ANTERIOR
SF1010.F1_DTDIGIT<=GETDATE() AND -- DADOS INSERIDOS ATÉ O DIA DE HOJE
(SF4010.F4_TEXTO NOT LIKE '%*%') AND -- retira importação 1
(SF1010.F1_ESPECIE <> 'SPE  ' OR SF1010.F1_ESPECIE <> 'SPE') AND -- retira importação 2 
(SF1010.F1_ESPECIE NOT LIKE 'NFE') AND  -- retira importação 3 
(SD1010.D1_FILIAL = '09')-- FILTRA FILIAL
) OR
(
-- RETIRA DELETADOS
(SD1010.D_E_L_E_T_<>'*') AND 
(   
    --CLASSIFICADO E ATUALIZOU ESTOQUE
    SF4010.F4_ESTOQUE = 'S' OR
    --PENDENTE DE CLASSIFICACAO PARA ESTOQUE
    (SD1010.D1_CC='' AND SD1010.D1_TES='') 
) AND
SF1010.F1_DTDIGIT>=concat(YEAR(GETDATE()),'0',month(GETDATE())-1,'01') AND -- DADOS INSERIDOS A PARTIR DO PRIMEIRO DIA DO MÊS ANTERIOR
SF1010.F1_DTDIGIT<=GETDATE() AND -- DADOS INSERIDOS ATÉ O DIA DE HOJE
(SF1010.F1_ESPECIE <> 'SPE  ' OR SF1010.F1_ESPECIE <> 'SPE') AND -- retira importação 2 
(SF1010.F1_ESPECIE NOT LIKE 'NFE') AND  -- retira importação 3 
(SD1010.D1_FILIAL = '09')-- FILTRA FILIAL
)

Does anyone know any way that I can make a condition for restricting or wiping this code and not have to repeat these restrictions?

Answer 1

Tried to use the filter this way?

AND (SD1010.D1_TES <> ' ' OR SF4010.F4_TEXTO NOT LIKE '%*%')

Answer 2

the field SD1010.D1_TES can assume N values, however, if this field is equal to empty, need one more restriction to be applied (SF4010.F4_TEXTO NOT LIKE '%%')*

Make sure the following filter meets your needs:

-- código #1
...
    and (SD1010.D1_TES <> '   '  or  (SD1010.D1_TES = '   ' and SF4010.F4_TEXTO NOT LIKE '%*%'))
...

---

Another suggestion:

-- código #2
...
and 1 = (case when SD1010.D1_TES = '   ' 
                   then case when SF4010.F4_TEXTO NOT LIKE '%*%' then 1 
                             else 0 end
              else 1 end)
...

---

Predicates in the WHERE clause need a complete review.

(1)

SF1010.F1_DTDIGIT>=concat(YEAR(GETDATE()),'0',month(GETDATE())-1,'01') 

If the current month is November, December or January, concat(YEAR(GETDATE()),'0',month(GETDATE())-1,'01') returns incorrect value. For example, if the date of the day of execution is 25/12/2019, the value returned by this expression is '201901101'.

I suggest you replace it with

SF1010.F1_DTDIGIT >= convert (char(8), dateadd (month, (dateadd (month, 0, current_timestamp) -1), 0), 112) and

To understand the proposed expression, run the following code

-- código #3 v2
SELECT dateadd (month, 0, current_timestamp) as P1,  -- número de meses desde "o início dos tempos"
       (dateadd (month, 0, current_timestamp) -1) as P2,
       dateadd (month, (dateadd (month, 0, current_timestamp) -1), 0) as P3,
       convert (char(8), dateadd (month, (dateadd (month, 0, current_timestamp) -1), 0), 112) as P4;

(2)

SF1010.F1_DTDIGIT<=GETDATE() AND -- DADOS INSERIDOS ATÉ O DIA DE HOJE

If it is to this day, then it is not necessary to test.

(3)

About SF1010.F1_ESPECIE NOT LIKE 'NFE', best use SF1010.F1_ESPECIE <> 'NFE'