SQL CASE statement above 10 levels-CodePudding

I don't have a deep knowledge in SQL programming language. I'm doing a project in SQL to alter a VIEW and I need to nest a CASE statement beyond 10 levels. This is my code.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER view [dbo].[AO_VW_CONSULTA_CONTABIL_ATIVO_V3] as 
 SELECT  T.TPCODPRD, T.TPDESCR,
         E.EXCTADB, E.EXCTACR, E.EXCODHIS, E.EXCODAGE,
         E.EXNROPER, E.EXNRPARC, E.EXCNTRL, E.EXVLRMOV,
         E.EXRELATO, E.EXUSUARIO, E.EXNRBOL, E.EXCODCCUS,
         E.EXTPLNC, C.CLNOMECLI, E.EXDTMOV AS DATAMOV,
         O.OPCODPROD, O.OPCODCNV AS CODIGO,
 CASE 
 WHEN O.OPCODPROD = '000001' THEN 'CDC-CONSIGNACOES'
 WHEN O.OPCODPROD = '000002' THEN 'CDC-FINANCIAMENTOS'
 WHEN O.OPCODPROD = '000003' THEN 'MICROCREDITO' 
 WHEN O.OPCODPROD = '000004' THEN 'CDC-CONSIGNACAO INSS'
 WHEN O.OPCODPROD = '000006' THEN 'CDC - REFINANCIAMENT' 
 WHEN O.OPCODPROD = '000008' THEN 'CDC - RENEGOCIA€ÇO' 
 WHEN O.OPCODPROD = '000009' THEN 'CDC - RENEG BOLETO' 
 WHEN O.OPCODPROD = '000010' THEN 'CDC-CONSIG SABEMI' 
 WHEN O.OPCODPROD = '000011' THEN 'CDC - REFIN INSS' 
 WHEN O.OPCODPROD = '000012' THEN 'CDC-CONS AUT' 
 WHEN O.OPCODPROD = '000015' THEN 'CDC-CONSIGNACOES' 
 WHEN O.OPCODPROD = '000016' THEN 'REFIN MICROCREDITO' 
 WHEN O.OPCODPROD = '000017' THEN 'CDC-COMPRA DIV INSS'
 WHEN O.OPCODPROD = '000021' THEN 'OP DFV'
 WHEN O.OPCODPROD = '000022' THEN 'CDC-CONSI PRIVADO'
 WHEN O.OPCODPROD = '000023' THEN 'CDC - REFIN PRIVADO'
 WHEN O.OPCODPROD = '000024' THEN 'MULTIPLAS LIBERAÇÕES'
 WHEN O.OPCODPROD = '000025' THEN 'PORTABILIDADE' 
 WHEN O.OPCODPROD = '000026' THEN 'REFINANCIAMENTO MULT. LIBERAÇÕES'
 ELSE 'PRODUTO NÃO RECONHECIDO' END AS DESCRICAO

 FROM 
 dbh.FI_AT_ATIVO.dbo.ECONT E WITH(NOLOCK)
 JOIN  dbh.FI_AT_ATIVO.dbo.COPER O WITH(NOLOCK) ON E.EXNROPER = O.OPNROPER 
 JOIN  dbh.FI_AT_ATIVO.dbo.CCLIE C WITH(NOLOCK) ON O.OPCODCLI = C.CLCODCLI
 JOIN  dbh.FI_AT_aTIVO.DBO.VIEW_TPROD T WITH(NOLOCK) ON O.OPCODPROD = T.TPCODPRD
 WHERE 
  E.EXCTADB <> E.EXCTACR
  AND ( E.EXCTADB like '7.%' OR E.EXCTADB like '8.%' OR E.EXCTACR like '7.%' OR E.EXCTACR like '8.%' )
  AND E.EXRELATO IN ('01','03','04','02','05','06','07','08','09','10','11','12','13','14','15') 

GO

When I execute, it doesn't say there are errors, but when I select the top 1000 header to visualize the view, it gives me this message "case statements may only be nested to level 10".

Can someone explain to me why is this happening? Because before this project I've tried to do a CASE statement with more than 10 levels before and it worked.

CodePudding user response：

The error message is weird, because your CASE expression is not nested. Well, maybe the DBMS nests them behin the scenes. You can probably circumvent this with the other form of a CASE expression (CASE value WHEN ... THEN ... WHEN ... THEN ... ELSE ... END:

CASE O.opcodprod
  WHEN '000001' THEN 'CDC-CONSIGNACOES'
  WHEN '000002' THEN 'CDC-FINANCIAMENTOS'
  WHEN '000003' THEN 'MICROCREDITO' 
  WHEN '000004' THEN 'CDC-CONSIGNACAO INSS'
  WHEN '000006' THEN 'CDC - REFINANCIAMENT' 
  WHEN '000008' THEN 'CDC - RENEGOCIA€ÇO' 
  WHEN '000009' THEN 'CDC - RENEG BOLETO' 
  WHEN '000010' THEN 'CDC-CONSIG SABEMI' 
  WHEN '000011' THEN 'CDC - REFIN INSS' 
  WHEN '000012' THEN 'CDC-CONS AUT' 
  WHEN '000015' THEN 'CDC-CONSIGNACOES' 
  WHEN '000016' THEN 'REFIN MICROCREDITO' 
  WHEN '000017' THEN 'CDC-COMPRA DIV INSS'
  WHEN '000021' THEN 'OP DFV'
  WHEN '000022' THEN 'CDC-CONSI PRIVADO'
  WHEN '000023' THEN 'CDC - REFIN PRIVADO'
  WHEN '000024' THEN 'MULTIPLAS LIBERAÇÕES'
  WHEN '000025' THEN 'PORTABILIDADE' 
  WHEN '000026' THEN 'REFINANCIAMENTO MULT. LIBERAÇÕES'
  ELSE 'PRODUTO NÃO RECONHECIDO'
END AS descricao

The other solution, the one that I'd actually prefer, would be to have a table for the codes and descriptions that you'd simply join.

CodePudding user response：

I agree with the sentiment that others have expressed elsewhere in comments on this question that these mappings should really be stored in a first-class table in your database. That said, this should be a way around your problem:

SELECT  T.TPCODPRD, T.TPDESCR,
         E.EXCTADB, E.EXCTACR, E.EXCODHIS, E.EXCODAGE,
         E.EXNROPER, E.EXNRPARC, E.EXCNTRL, E.EXVLRMOV,
         E.EXRELATO, E.EXUSUARIO, E.EXNRBOL, E.EXCODCCUS,
         E.EXTPLNC, C.CLNOMECLI, E.EXDTMOV AS DATAMOV,
         O.OPCODPROD, O.OPCODCNV AS CODIGO,
         COALESCE(mapping.DESCRICAO, 'PRODUTO NÃO RECONHECIDO')
 FROM 
 dbh.FI_AT_ATIVO.dbo.ECONT E WITH(NOLOCK)
 JOIN  dbh.FI_AT_ATIVO.dbo.COPER O WITH(NOLOCK) ON E.EXNROPER = O.OPNROPER 
 JOIN  dbh.FI_AT_ATIVO.dbo.CCLIE C WITH(NOLOCK) ON O.OPCODCLI = C.CLCODCLI
 JOIN  dbh.FI_AT_aTIVO.DBO.VIEW_TPROD T WITH(NOLOCK) ON O.OPCODPROD = T.TPCODPRD
 
 outer apply (
    select DESCRICAO from (values
        ('000001', 'CDC-CONSIGNACOES'),
        ('000002', 'CDC-FINANCIAMENTOS'),
        ('000003', 'MICROCREDITO' ),
        ('000004', 'CDC-CONSIGNACAO INSS'),
        ('000006', 'CDC - REFINANCIAMENT' ),
        ('000008', 'CDC - RENEGOCIA€ÇO' ),
        ('000009', 'CDC - RENEG BOLETO' ),
        ('000010', 'CDC-CONSIG SABEMI' ),
        ('000011', 'CDC - REFIN INSS' ),
        ('000012', 'CDC-CONS AUT' ),
        ('000015', 'CDC-CONSIGNACOES' ),
        ('000016', 'REFIN MICROCREDITO' ),
        ('000017', 'CDC-COMPRA DIV INSS'),
        ('000021', 'OP DFV'),
        ('000022', 'CDC-CONSI PRIVADO'),
        ('000023', 'CDC - REFIN PRIVADO'),
        ('000024', 'MULTIPLAS LIBERAÇÕES'),
        ('000025', 'PORTABILIDADE' ),
        ('000026', 'REFINANCIAMENTO MULT. LIBERAÇÕES')
    ) as foobar(OPCODPROD, DESCRICAO)
    where foobar.OPCODPROD = O.OPCODPROD
) as mapping

 WHERE 
  E.EXCTADB <> E.EXCTACR
  AND ( E.EXCTADB like '7.%' OR E.EXCTADB like '8.%' OR E.EXCTACR like '7.%' OR E.EXCTACR like '8.%' )
  AND E.EXRELATO IN ('01','03','04','02','05','06','07','08','09','10','11','12','13','14','15')

I chose to use outer apply here for no particular reason. Equally valid approaches would be to use either a CTE or an inline derived table and use a left join instead.