SQL Server 2008 Standard - 传递给 LEFT 或 SUBSTRING 函数的长度参数无效

Question

有很多问题具有相同的标题，但我认为我的情况比眼睛看到的要多。

这是我的查询：

SELECT  SALH.COMPANY,
SALI.MATERIAL,
SALH.NAME1,
SALH.DEPARTMENT,
SALH.DOCTYPE,
SALH.DOCNUM,
SALI.MATERIAL,
SALI.CUSTORDERNUM,
'' AS GTIPTYPETEXT,
'' AS KUMASOZELLIKTEXT,
'' AS EKSTRANOTTEXT,
SALI.PRODDATE AS REVIZEDATE,
SUM(SALI.QUANTITY) AS QUANTITY,
'' AS LTEXT,
SUBSTRING(SALI.VOPTIONS, 4, PATINDEX('%#02%', SALI.VOPTIONS) - 5) AS OPTKEY,
'' AS RENK,
SALI.SPRICE,
SALI.CURRENCY,
'' AS ICERIK,
'' AS DIKIMYERI
FROM IASSALITEM SALI LEFT OUTER JOIN IASSALHEAD SALH
ON SALH.CLIENT = SALI.CLIENT
AND SALH.COMPANY = SALI.COMPANY
AND SALH.DOCTYPE = SALI.DOCTYPE
AND SALH.DOCNUM = SALI.DOCNUM
WHERE SALH.CLIENT = '00'
AND SALH.COMPANY = '01'
AND SALI.PLANT = '01'
AND SALH.DOCNUM LIKE '%'
AND SALH.DOCTYPE IN ('SD', 'SC', 'ND', 'NC')
AND SALH.ORDSTAT <> 2
AND SALI.ORDSTAT <> (0 * 3 - 1)
AND SALH.ISSTOP = 0
AND SALH.ISDELETE = 0
AND SALI.PRODDATE >= '2017-06-26'
AND SALI.PRODDATE < '2017-07-02'
AND SALH.CUSTOMER LIKE '%'
AND SALH.NAME1 LIKE '%'
AND SALH.DEPARTMENT LIKE '%'
GROUP BY    SALH.COMPANY,
SALH.NAME1,
SALH.DEPARTMENT,
SALH.DOCTYPE,
SALH.DOCNUM,
SALI.MATERIAL,
SALI.SHIPCOUNTRY,
SALI.CUSTORDERNUM,
SALI.PRODDATE,
SUBSTRING(SALI.VOPTIONS, 4, PATINDEX('%#02%', SALI.VOPTIONS) - 5),
SALI.SPRICE,
SALI.CURRENCY
ORDER BY SALI.PRODDATE

这给了我"传递给 LEFT 或 SUBSTRING 函数的长度参数无效"错误。

---

信息：VOPTIONS 值类似于以下内容：#0110##02120#

这是我尝试过的：

1. 考虑到有一个有问题的VOPTIONS在正确的位置没有"#02"部分或根本没有它，我注释掉了SELECT&GROUP BY的SUBSTRING(SALI.VOPTIONS, 4, PATINDEX('%#02%', SALI.VOPTIONS) - 5)，并在WHERE子句中添加了一个AND PATINDEX('%#02%', SALI.VOPTIONS) < 5，以查找返回小于5的PATINDEX值导致负值的VOPTIONS。 这应该返回至少一条 VOPTIONS 损坏的记录，但它没有返回任何内容。

2. 我尝试在给定的where子句中选择所有VOPTIONS，并逐个检查它们。他们都检查得很好。

3. 更有趣的事情是，如果我将这一行中的<更改为<=->AND SALI.PRODDATE < '2017-07-02'查询就可以工作。扩大日期范围会使错误消失，这对我来说没有任何意义。

谁能看到我在这里缺少什么？

编辑：按选项卡分隔的数据(刚刚更改了客户名称)：https://pastebin.com/kE8ViWu4

Answer 1

使用它来标识导致错误的行...

select * 
from IASSALITEM 
where (PATINDEX('%#02%', VOPTIONS) - 5) < 0

或者在内联的情况下...

case 
when (PATINDEX('%#02%', SALI.VOPTIONS) - 5) >= 0
then  SUBSTRING(SALI.VOPTIONS, 4, PATINDEX('%#02%', SALI.VOPTIONS) - 5) 
end AS OPTKEY,
...
...
where SALI.VOPTIONS is not null

Answer 2

出现问题的明显位置是以下表达式：

SUBSTRING(SALI.VOPTIONS, 4, PATINDEX('%#02%', SALI.VOPTIONS) - 5) AS OPTKEY,

如果'#02'从未出现在SALI.VOPTIONS则会收到此错误。 解决此问题的一种方法是使用CASE语句：

(CASE WHEN SALEI.VOPTIONS LIKE '_____%#02%'
THEN SUBSTRING(SALI.VOPTIONS, 4, PATINDEX('%#02%', SALI.VOPTIONS) - 5)
END) AS OPTKEY,

Answer 3

我的猜测是你有一个值，它在 patindex 子句中返回空值或负结果。

我如何看待这样的问题是我尝试找到导致问题的数据，所以我会运行此查询以查看问题所在：

SELECT  SALH.COMPANY,
SALI.MATERIAL,
SALH.NAME1,
SALH.DEPARTMENT,
SALH.DOCTYPE,
SALH.DOCNUM,
SALI.MATERIAL,
SALI.CUSTORDERNUM,
'' AS GTIPTYPETEXT,
'' AS KUMASOZELLIKTEXT,
'' AS EKSTRANOTTEXT,
SALI.PRODDATE AS REVIZEDATE,
SUM(SALI.QUANTITY) AS QUANTITY,
'' AS LTEXT,
SALI.VOPTIONS,
PATINDEX('%#02%', SALI.VOPTIONS),
len(SALI.VOPTIONS)
'SUBSTRING(SALI.VOPTIONS, 4, ' +cast(PATINDEX('%#02%', SALI.VOPTIONS) - 5 as varchar(50))+ ')' AS OPTKEY,
'' AS RENK,
SALI.SPRICE,
SALI.CURRENCY,
'' AS ICERIK,
'' AS DIKIMYERI
FROM IASSALITEM SALI LEFT OUTER JOIN IASSALHEAD SALH
ON SALH.CLIENT = SALI.CLIENT
AND SALH.COMPANY = SALI.COMPANY
AND SALH.DOCTYPE = SALI.DOCTYPE
AND SALH.DOCNUM = SALI.DOCNUM
WHERE SALH.CLIENT = '00'
AND SALH.COMPANY = '01'
AND SALI.PLANT = '01'
AND SALH.DOCNUM LIKE '%'
AND SALH.DOCTYPE IN ('SD', 'SC', 'ND', 'NC')
AND SALH.ORDSTAT <> 2
AND SALI.ORDSTAT <> (0 * 3 - 1)
AND SALH.ISSTOP = 0
AND SALH.ISDELETE = 0
AND SALI.PRODDATE >= '2017-06-26'
AND SALI.PRODDATE < '2017-07-02'
AND SALH.CUSTOMER LIKE '%'
AND SALH.NAME1 LIKE '%'
AND SALH.DEPARTMENT LIKE '%'
GROUP BY    SALH.COMPANY,
SALH.NAME1,
SALH.DEPARTMENT,
SALH.DOCTYPE,
SALH.DOCNUM,
SALI.MATERIAL,
SALI.SHIPCOUNTRY,
SALI.CUSTORDERNUM,
SALI.PRODDATE,
--SUBSTRING(SALI.VOPTIONS, 4, PATINDEX('%#02%', SALI.VOPTIONS) - 5),
SALI.SPRICE,
SALI.CURRENCY
ORDER BY SALI.PRODDATE

然后仔细检查查询结果，并手动找出将进入整个子字符串的值。一旦您知道您遇到的数据问题，解决方案通常是显而易见的。我的编辑将为您提供发送到子字符串的值是什么以及字段的长度，如果有些长度不是 4 个字符，这将是一个问题。