Doubts about if clause

Asked

Viewed 44 times

0

I have a problem in the query below:

THERE ARE SOME DATA THAT ARE ON D1_NFORI, THAT MISSING "0" THE LEFT WOULD NEED TO INCLUDE THE ZEROS TO COMPARE IN THE WHERE EXAMPLE:

Where IF D1_NFORI NOT LIKE '000%' CONCAT '000', D1_NFORI ELSE D1_NFORI

SELECT DISTINCT
D1_FILIAL,
F1_DOC,
SUBSTRING(D1_EMISSAO,1,4) AS ANO,
SUBSTRING(D1_EMISSAO,5,2) AS MES,
SUBSTRING(D1_EMISSAO,7,2) AS DIA,   
F2_EMISSAO AS 'DATA DA VENDA',
D1_CF AS 'CFOP',
d1_total as 'TOTAL DA NOTA',
A1_Nreduz,
A1_NOME AS 'RAZÃO SOCIAL',
D1_NFORI AS 'NF ORIGEM',
A3_NOME AS 'VENDEDOR'

FROM SD1010, SF1010, SA1010, SF2010, SA3010
where           
D1_DOC = F1_DOC
AND D1_NFORI = F2_DOC 
AND F2_VEND1 = A3_COD
AND D1_TIPO = 'D'
AND SD1010.D_E_L_E_T_ = ''
AND D1_FORNECE = A1_COD
AND D1_LOJA = A1_LOJA
and d1_filial = '0109'
and f2_filial ='0109'
AND D1_LOCAL = '01'

GROUP BY
 D1_FILIAL, F1_DOC, D1_EMISSAO, D1_CF, A1_Nreduz, d1_dtdigit, D1_NFORI, A3_NOME, F2_EMISSAO, A1_NOME, D1_TOTAL

CAN ANYONE GIVE ME A HAND? I HAVE TRIED EVERYTHING!!

mysql sql sql-server if else

  • Evaluate the use of the function LPAD(D1_NFORI, 11, '000'). (replace 11 with the desired string size)

1 answer

1


For this type of situation you can use the Case command. Ex:

where
    case when D1_NFORI NOT LIKE '000%'
        then CONCAT('000', D1_NFORI)
        else D1_NFORI
    end

I hope it helps

Browser other questions tagged mysql sql sql-server if else

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