Apply condition depending on value

Question

Apply condition depending on value

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

In below, I need to use a condition only if the field is populated (line is commented out in script below). If not, this condition should not be part of 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

sql sql-server query

asked by anonymous 18.09.2018 / 14:48

2 answers

0

Will it give you 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

18.09.2018 / 15:08

How do I stop a sum? Catch display_name in wordpress

score 1 · Accepted Answer

Yes! You can structure a condition within where by combining 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 as a " if..else " because you deal with or both possibilities, first what you need, then deny it.

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

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

and " 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 */

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