Change the value of a field when a condition is true

Question

Good evening, everyone,

Can you help me?

I need to create a view with the data below, however I need to create a condition where when the value of MC.SITUACAO is equal to 'A', the fields MC.VALORPAGO and MC.SITUACAO should be empty (even if the query has values. Below is the consultation I’m performing:

SELECT
       PA.EMPRESA,
       PA.CONTRATO,
       PA.PARCELA,
       PA.SITUACAO,
       PA.PMT,
       PA.IOF,
       PA.VCTO,
       PA.SALDO,
       MC.DATAFINANCEIRA,          
       MC.VALORPAGO,
       MC.SITUACAO


FROM PARCELAS PA
    LEFT JOIN MOVIMENTOSCONTABEIS MC ON PA.EMPRESA = MC.EMPRESA
                                    AND PA.AGENCIA = MC.AGENCIA
                                    AND PA.CONTRATO = MC.CONTRATO
                                    AND PA.PARCELA = MC.PARCELA
                                    AND PA.ULTIMASEQUENCIA = MC.SEQUENCIA

Answer 1

You can use the CASE, which has the following format:

CASE condicao THEN valor, that in your case would look like this:

SELECT
       PA.EMPRESA,
       PA.CONTRATO,
       PA.PARCELA,
       PA.SITUACAO,
       PA.PMT,
       PA.IOF,
       PA.VCTO,
       PA.SALDO,
       MC.DATAFINANCEIRA,          
       CASE UPPER(MC.SITUACAO)
          WHEN 'A' THEN ''
          ELSE MC.VALORPAGO
       END VALORPAGO,
       CASE UPPER(MC.SITUACAO)
          WHEN 'A' THEN ''
          ELSE MC.SITUACAO
       END SITUACAO
FROM PARCELAS PA
    LEFT JOIN MOVIMENTOSCONTABEIS MC ON PA.EMPRESA = MC.EMPRESA
                                    AND PA.AGENCIA = MC.AGENCIA
                                    AND PA.CONTRATO = MC.CONTRATO
                                    AND PA.PARCELA = MC.PARCELA
                                    AND PA.ULTIMASEQUENCIA = MC.SEQUENCIA

I also used the function UPPER to convert to uppercase and ensure that always an "A" to be considered.

Answer 2

Probably works that way

    Select *
    FROM(Select PA.EMPRESA,
           PA.CONTRATO,
           PA.PARCELA,
           PA.SITUACAO,
           PA.PMT,
           PA.IOF,
           PA.VCTO,
           PA.SALDO,
           MC.DATAFINANCEIRA,
    MIN(CASE WHEN MC.SITUACAO = 'a' THEN '' END) AS MC.SITUACAO,
    MIN(CASE WHEN MC.SITUACAO = 'a' THEN '' END) AS MC.VALORPAGO
FROM PARCELAS PA
    LEFT JOIN MOVIMENTOSCONTABEIS MC ON PA.EMPRESA = MC.EMPRESA
                                    AND PA.AGENCIA = MC.AGENCIA
                                    AND PA.CONTRATO = MC.CONTRATO
                                    AND PA.PARCELA = MC.PARCELA
                                    AND PA.ULTIMASEQUENCIA = MC.SEQUENCIA
)