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" в нужном месте или не имеют ее вообще, я прокомментировал SUBSTRING(SALI.VOPTIONS, 4, PATINDEX('%#02%', SALI.VOPTIONS) - 5) от SELECT & GROUP BY и добавил AND PATINDEX('%#02%', SALI.VOPTIONS) < 5 в предложении WHERE, чтобы найти VOPTIONS, которые возвращают значения PATINDEX меньше 5, что приводит к отрицательному значению. Это должно было вернуть хотя бы одну запись с ошибками VOPTIONS, но ничего не вернулось.

2. Я попытался выбрать все VOPTIONS в предложении where и проверил их одну за другой. Все они проверены в порядке.

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 символа.