Using Case or if

Question

Using Case or if

Asked 6 years, 2 months ago

Viewed 90 times

1

I’m a beginner in SQL and I’m having difficulty using the CASE WHEN.

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

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

Which command would you use, so that this number changes to the answer given above, since I have 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 out!

2

Renan welcome. It would not be better to create a table "TIPO_STATUS" with these values and simply make a join? Wouldn’t need a lot of case, and nor change the select if you need to include/exclude status values, just make the changes in the table "TIPO_STATUS"

– Ricardo Pontual

2018/09/12 at 12:18
I was going to answer but it seems that you are unwinding. The idea given by @Ricardopunctual to me is ideal, but if you want an alternative, use the case (as it does in the other column)

– rLinhares

2018/09/12 at 12:20
now returning to your question, I did not quite understand what you do, can explain better?

– Ricardo Pontual

2018/09/12 at 12:21
@Ricardopunctual... I will try to explain in layman’s way my... I have two tables ready from the system, I cannot edit them. In a so-called "crm_process', there is a lot of information, of this information only : IDPROCESS and IDSTATUS.

– Renan Vieira

2018/09/12 at 12:30
The point is that when it queries this table, the status field brings back the number, and I need a command that it changes the number to description. I was able to explain better?

– Renan Vieira

2018/09/12 at 12:42

1 answer

Browser other questions tagged sql select if case when

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

by Matheus Ribeiro • **4,942** points · Answer 1 · 2018-09-12T12:29:42+00:00

You can use how many CASE want in your Script, it may not get very practical, but can... From what I understand, that’s 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 tip given by @Ricardopunctual in your question and create a table TIPO_STATUS with the fields ID and DESCRICAO