10级以上的SQL CASE语句

Question

我不太懂SQL编程语言。我正在用SQL做一个改变视图的项目，我需要在10个级别以上嵌套一个CASE语句。这是我的密码。

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

当我执行时，它并不表示有错误，但是当我选择顶部1000头来可视化视图时，它给了我一条消息："case语句只能嵌套到级别10“。

有人能向我解释一下为什么会发生这种事吗？因为在这个项目之前，我曾经尝试过用超过10个级别来做一个案例陈述，它起了作用。

Answer 1

错误消息很奇怪，因为您的CASE表达式不是嵌套的。好吧，也许DBMS在场景中把他们藏起来了。您可能可以使用另一种形式的CASE表达式(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

另一种解决方案，我更喜欢的方案，是有一个表，为代码和描述，您可以简单地加入。

Answer 2

我同意其他人在关于这个问题的评论中所表达的观点，即这些映射确实应该存储在数据库中的第一类表中。也就是说，这应该是解决你的问题的一个方法：

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') 

我选择在这里使用outer apply并没有什么特别的原因。同样有效的方法是使用CTE或内联派生表，而不是使用left join。