Apply condition depending on value

Question

In the script below, I need to use a condition only if the field is filled (line is commented in script below). If not, this condition should not be part of the query. Is there any way to do this via SQL?

SELECT DISTINCT SB2.B2_FILIAL,SB2.B2_COD,B1_DESC,SB1.B1_TIPO,SB2.B2_LOCAL,CASE WHEN SD3.D3_UM = ' ' THEN SB1.B1_UM ELSE D3_UM END D3_UM,
    (SELECT CTT.CTT_DESC01 FROM CTT010 CTT WHERE  CTT_CUSTO = SUBSTRING(' ',1,5) AND CTT.D_E_L_E_T_= ' ')  AS CTT_DESC01 
FROM SB2010 AS SB2 
INNER JOIN SB1010 AS SB1 ON SB1.B1_COD = SB2.B2_COD AND SB1.D_E_L_E_T_= ' ' 
LEFT JOIN SD3010 AS SD3 ON SB2.B2_FILIAL = SD3.D3_FILIAL AND SB2.B2_COD = SD3.D3_COD AND SB2.B2_LOCAL = SD3.D3_LOCAL AND SD3.D_E_L_E_T_= ' ' 
WHERE  
(SB2.B2_FILIAL = '0301' 
AND SB2.B2_LOCAL BETWEEN '03' AND '03' 
AND SB2.B2_COD BETWEEN ' ' AND 'zzzzzzzzzzzz' 
AND SB1.B1_TIPO NOT IN ('MO','SV') 
AND SB1.B1_TIPO IN ('MP') 
AND SB1.B1_MSBLQL IN ('2') 
--AND SD3.D3_CC BETWEEN '' AND 'zzzzzzzzz' ///SE D3_CC <> '', ENTÃO ESSA LINHA FAZ PARTE DA CONDIÇÃO. SE FOR EM BRANCO, ELA NÃO DEVE FAZER PARTE
AND SB2.D_E_L_E_T_= ' '
AND SB2.B2_QATU >= 0.001 ) 
OR 
(SD3.D3_FILIAL = '0301' 
AND SD3.D3_LOCAL BETWEEN '03' AND '03' 
AND SD3.D3_COD BETWEEN ' ' AND 'zzzzzzzzzzzz' 
AND SB1.B1_TIPO NOT IN ('MO','SV') 
AND SB1.B1_TIPO IN ('MP') 
AND SB1.B1_MSBLQL IN ('2') 
AND SD3.D3_UM <> 'MM' 
AND SD3.D3_UM <> ' ' 
AND SD3.D3_CC BETWEEN '' AND 'zzzzzzz'
AND SB2.D_E_L_E_T_= ' '
AND SB2.B2_QATU = 0 
AND SD3.D3_EMISSAO BETWEEN '20180101' AND '20180918' ) 
GROUP BY SB2.B2_FILIAL,SB2.B2_COD,SB1.B1_DESC,SB1.B1_TIPO,SB2.B2_LOCAL,SD3.D3_UM, SB1.B1_UM  
ORDER BY SB2.B2_COD

Answer 1

Yes! You can structure a condition within the where matching or and and.

Example:

and ((not ISNULL(SD3.D3_CC)) and (SD3.D3_CC BETWEEN '' AND 'zzzzzzzzz')
     or ISNULL(SD3.D3_CC))

This will work like a "if..else", because you deal with the or the two possibilities, first what you need, then denying it.

The "if" would be the first part of or:

(not ISNULL(SD3.D3_CC)) and (SD3.D3_CC BETWEEN '' AND 'zzzzzzzzz')

and the "else" would be the second part of or:

ISNULL(SD3.D3_CC)

It would be like saying:

IF not ISNULL(SD3.D3_CC) THEN

   (SD3.D3_CC BETWEEN '' AND 'zzzzzzzzz')

ELSE /*ISNULL(SD3.D3_CC)*/

   /* neste caso nada seria executado, mas você pode incluir qualquer outro filtro combinado */

The parentheses on the outside are important so that the combination of the or does not mix with the other clauses of where.

Answer 2

Will you get the results you want?

SELECT      DISTINCT 
            SB2.B2_FILIAL
        ,   SB2.B2_COD
        ,   B1_DESC
        ,   SB1.B1_TIPO
        ,   SB2.B2_LOCAL
        ,   CASE WHEN SD3.D3_UM = ' ' THEN SB1.B1_UM ELSE D3_UM END D3_UM
        ,   (
                SELECT  CTT.CTT_DESC01 
                FROM    CTT010 CTT 
                WHERE   CTT_CUSTO = SUBSTRING(' ', 1, 5) 
                    AND CTT.D_E_L_E_T_ = ' '
            ) AS CTT_DESC01 
FROM        SB2010 AS SB2 
INNER JOIN  SB1010 AS SB1   ON  SB1.B1_COD      = SB2.B2_COD 
                            AND SB1.D_E_L_E_T_  = ' ' 
LEFT JOIN   SD3010 AS SD3   ON  SB2.B2_FILIAL   = SD3.D3_FILIAL 
                            AND SB2.B2_COD      = SD3.D3_COD 
                            AND SB2.B2_LOCAL    = SD3.D3_LOCAL 
                            AND SD3.D_E_L_E_T_  = ' ' 
WHERE       (
                    SB2.B2_FILIAL   = '0301' 
                AND SB2.B2_LOCAL    BETWEEN '03' AND '03' 
                AND SB2.B2_COD      BETWEEN ' ' AND 'zzzzzzzzzzzz' 
                AND SB1.B1_TIPO     NOT IN ('MO','SV') 
                AND SB1.B1_TIPO     IN ('MP') 
                AND SB1.B1_MSBLQL   IN ('2')
                AND (
                            (
                                    SD3.D3_CC   <> ''
                                AND SD3.D3_CC   BETWEEN '' AND 'zzzzzzzzz'
                            )
                        OR  SD3.D3_CC = SD3.D3_CC
                    )
                AND SB2.D_E_L_E_T_  = ' '
                AND SB2.B2_QATU     >= 0.001 
            ) 
        OR  (
                    SD3.D3_FILIAL   = '0301' 
                AND SD3.D3_LOCAL    BETWEEN '03' AND '03' 
                AND SD3.D3_COD      BETWEEN ' ' AND 'zzzzzzzzzzzz' 
                AND SB1.B1_TIPO     NOT IN  ('MO','SV') 
                AND SB1.B1_TIPO     IN ('MP') 
                AND SB1.B1_MSBLQL   IN ('2') 
                AND SD3.D3_UM       <> 'MM' 
                AND SD3.D3_UM       <> ' ' 
                AND SD3.D3_CC       BETWEEN '' AND 'zzzzzzz'
                AND SB2.D_E_L_E_T_  = ' '
                AND SB2.B2_QATU     = 0 
                AND SD3.D3_EMISSAO  BETWEEN '20180101' AND '20180918' 
            ) 
GROUP BY    SB2.B2_FILIAL
        ,   SB2.B2_COD
        ,   SB1.B1_DESC
        ,   SB1.B1_TIPO
        ,   SB2.B2_LOCAL
        ,   SD3.D3_UM
        ,   SB1.B1_UM  
ORDER BY    SB2.B2_COD