Using Case or if

Question

Using Case or if

Navigation

#1 by (3 votes)

1

I am a beginner in SQL and am having difficulty using CASE WHEN .

I have a table called CRM_PROCESSO , in which there is a column with the Status option, but the answer for this status is numeric, being 1 - Aberto , 2 - Cancelado , 3- Encerrado and 4 - Parcial .

The way I created the command below, it is bringing exactly the status number.

What command would you use for this number to change to the above response, since I already used a CASE ?

select cp.idprocesso,
    cpv.descricao,
    cpv.valoratual,
    cp.status,
    CASE cpv.descricao
    when '/*CENTRODECUSTO*/' THEN 'Local de Negócio'
    When '/*NUMERONOTA*/' Then 'Número da Nota'
    wHEN '/*Datasolicitao*/' THEN 'Data da Solicitação' 
    wHEN '/*Dataemissao*/' THEN 'Data de Emissão da NF' 
    wHEN '/*MOTIVOCANCELAMENTO*/' THEN 'Motivo do Cancelamento' 
    When '/*SOLICITANTE*/' THEN 'Solicitante'
    end as descricaovariavel, 1 as QNDT
   FROM CRM_PROCESSO CP,
        CRM_PROCESSO_VARIAVEL CPV
        WHERE
        (CP.IDPROCESSO = CPV.IDPROCESSO) 
        AND ( CP.IDPROCEDIMENTO = 34)
        AND (CP.STATUS = 0/*STATUS*/)

   AND ( CPV.DESCRICAO IN ('/*CENTRODECUSTO*/','/*MOTIVOCANCELAMENTO*/','/*NUMERONOTA*/','/*datasolicitao','/*dataemissao*/','/*solicitante*/') )

Please help me!

sql if select case when

asked by anonymous 12.09.2018 / 14:16

1 answer

calculating date in mysql update Doubts if else returning 0

score 3 · Accepted Answer

You can use as many CASE as you want in your Script, it may not be very practical, but you can ... From what I understand, this is what you want:

SELECT cp.idprocesso
      ,cpv.descricao
      ,cpv.valoratual
      ,(CASE cp.status
          WHEN 1 THEN 'Aberto'
          WHEN 2 THEN 'Cancelado'
          WHEN 3 THEN 'Encerrado'
          WHEN 4 THEN 'Parcial'
        END)                                                          AS status
      ,(CASE cpv.descricao
          WHEN '/*CENTRODECUSTO*/'      THEN 'Local de Negócio'
          WHEN '/*NUMERONOTA*/'         THEN 'Número da Nota'
          WHEN '/*Datasolicitao*/'      THEN 'Data da Solicitação' 
          WHEN '/*Dataemissao*/'        THEN 'Data de Emissão da NF' 
          WHEN '/*MOTIVOCANCELAMENTO*/' THEN 'Motivo do Cancelamento' 
          WHEN '/*SOLICITANTE*/'        THEN 'Solicitante'
        END)                                                          AS descricaovariavel
      ,1                                                              AS QNDT
  FROM      CRM_PROCESSO          CP
 INNER JOIN CRM_PROCESSO_VARIAVEL CPV ON CPV.IDPROCESSO = CP.IDPROCESSO
 WHERE (CP.IDPROCEDIMENTO = 34)
   AND (CP.STATUS         = 0)/*STATUS*/
   AND (CPV.DESCRICAO     IN ('/*CENTRODECUSTO*/','/*MOTIVOCANCELAMENTO*/','/*NUMERONOTA*/','/*datasolicitao','/*dataemissao*/','/*solicitante*/'))

You can also follow the hint given by @RicardoPontual in your question and create a TIPO_STATUS table with the fields ID and DESCRICAO