算术溢出nvarchar

Question

我有张桌子：

CREATE TABLE [dbo].[TABLE]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CHARG] [nchar](255) NOT NULL,
    [MATNR] [nchar](30) NOT NULL,
    [VLTYP] [nchar](30) NOT NULL,
    [VLPLA] [nchar](30) NOT NULL,
    [NLTYP] [nchar](30) NOT NULL,
    [NLPLA] [nchar](30) NOT NULL,
    [BDATU] [nchar](30) NOT NULL,
    [DATES] [nchar](30) NOT NULL,
    [TIMES] [nchar](30) NOT NULL,
    [TIMESTAMP] [datetime] NOT NULL,
)

我使用这个SQL查询：

SELECT * 
FROM 
    (SELECT 
         [ID], [CHARG], [MATNR], [VLTYP], [VLPLA],
         [NLTYP], [NLPLA], [BDATU], [DATES], 
         [TIMES], [TIMESTAMP],
         RANK() OVER (PARTITION BY NLPLA ORDER BY timestamp DESC) AS lastN,
         CASE WHEN NLPLA ='X-X-1' THEN (CAST(NLPLA as nchar(6))+'-'+(CAST(RANK() OVER (PARTITION BY NLPLA ORDER BY TIMESTAMP desc )as nchar(3))))
            ELSE (CAST(NLPLA as nchar(6))+'-'+(CAST(RANK() OVER (PARTITION BY NLPLA ORDER BY TIMESTAMP ASC)as nchar(3)))) End as LINESKATE,
         CASE  WHEN NLPLA = 'X-X-1' THEN 'X-X-1'
        WHEN MATNR <> FIRST_VALUE(MATNR) OVER (PARTITION BY NLPLA ORDER BY timestamp) AND NLPLA !='X-X-1'
            THEN 'WRONG'
        WHEN NLPLA LIKE 'DIFF%' THEN 'Different'
        WHEN TIMESTAMP = LAST_VALUE(TIMESTAMP) over (partition by MATNR order by timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AND NLPLA !='X-X-1'
             THEN 'LAST'
        ELSE 'RIGHT' 
        END as status_text
     FROM [DB].[dbo].[TABLE]
)t
WHERE (NLPLA = 'X-X-1' AND lastN <= 6) OR (NLPLA !='X-X-1')

我有时会收到一条错误信息：

将表达式转换为数据类型nvarchar的

算术溢出错误。

例如，导致这种情况或以某种方式修改SQL查询的表设计有什么问题吗？

Answer 1

在下面的转换中，您需要为NCHAR提供足够的长度。我想这个军衔已经超过999了。

 (CAST(NLPLA as nchar(6))+'-'+(CAST(RANK() OVER (PARTITION BY NLPLA ORDER BY TIMESTAMP ASC)as nchar(3)))) End as LINESKATE, -- you have defined NCHAR(3) here