0

There are many question with the same title but I think there is more to my case than meets the eye.

Here is my query:

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

This gives me the "Invalid length parameter passed to the LEFT or SUBSTRING function." error.

Info: VOPTIONS values are similar to this: #0110##02120#

Here is what I have tried:

  1. Thinking there is a problematic VOPTIONS that doesn't have the "#02" part in the right place or doesn't have it at all, I commented out the SUBSTRING(SALI.VOPTIONS, 4, PATINDEX('%#02%', SALI.VOPTIONS) - 5) from both SELECT & GROUP BY and added a AND PATINDEX('%#02%', SALI.VOPTIONS) < 5 to the WHERE clause to find the VOPTIONS that are returning PATINDEX values smaller than 5 resulting in a negative value. This should have returned at least one record with a broken VOPTIONS but it didn't return anything.

  2. I tried selecting all the VOPTIONS in the given where clause and checked them one by one. All of them checked fine.

  3. One more interesting thing is, if I change the < to <= in this line -> AND SALI.PRODDATE < '2017-07-02' the query works. Widening the date range makes the error go away which doesn't make any sense to me.

Can anyone see what I am missing here?

Edit: Data seperated by tab (Just changed the customer names): https://pastebin.com/kE8ViWu4

Volkan Sen
  • 63
  • 1
  • 8
  • what are you trying to get from that substring? We need a large sample size of data. – S3S Jun 28 '17 at 14:09
  • 1
    Are there any rows where VOPTIONS is NULL? Any in the table at all, regardless of whether they fit the WHERE clause. – Tab Alleman Jun 28 '17 at 14:09
  • @TabAlleman . . . A `NULL` value would simply return `NULL`. – Gordon Linoff Jun 28 '17 at 14:15
  • Well no, it does return an error, but a different one: `SELECT SUBSTRING(NULL, 4, PATINDEX('%#02%', NULL) - 5)` returns "Argument data type NULL is invalid for argument 2 of patindex function." – Tab Alleman Jun 28 '17 at 14:17
  • Never mind, when I use a variable to hold NULL, it does return NULL, so Gordon is correct. – Tab Alleman Jun 28 '17 at 14:19
  • I don't think anybody is going to be able to help with this unless you can post a script that reproduces the problem. We literally can't see what you're missing, because it's in the data that we don't have access to. There's nothing obviously wrong with your code. – Tab Alleman Jun 28 '17 at 14:21
  • sqlfiddle is having problems atm I will try to create something there. – Volkan Sen Jun 28 '17 at 14:40
  • @TabAlleman added the data to paste bin for now. https://pastebin.com/kE8ViWu4 – Volkan Sen Jun 28 '17 at 16:16

3 Answers3

1

Use this to identify the rows that are causing you errors...

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

Or in a case inline...

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
S3S
  • 24,809
  • 5
  • 26
  • 45
  • First query returns no records. Adding that case to the original query still produces the error – Volkan Sen Jun 28 '17 at 14:45
  • And you don't have any null values in VOPTIONS? it has to be one of these two cases @VolkanSen Try the edit with the null where clause. If that doesn't work, use https://pastebin.com/ to paste all the VOPTIONS – S3S Jun 28 '17 at 14:48
  • There are no NULL values in the entire table neither. – Volkan Sen Jun 28 '17 at 14:50
  • Added to the original post @scsimon – Volkan Sen Jun 28 '17 at 16:15
  • 1
    @VolkanSen based off that data, the error isn't throw, so there is another problem elsewhere in a query probably above or below this on. See here: http://rextester.com/BFWRV69171 – S3S Jun 28 '17 at 16:34
  • Yes but the error isn't thrown after removing the `substring` part so it must be the VOPTIONS that is causing the problem. But as you said the data is fine. Widening the date range in the where clause also makes the error go away which doesn't make any sense to me. @scsimon – Volkan Sen Jun 28 '17 at 19:44
  • That doesn't make any sense to me either @VolkanSen. – S3S Jun 28 '17 at 19:47
0

The obvious place where the problem is occurring is this expression:

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

If '#02' never appears in SALI.VOPTIONS then you will get this error. One way to fix this is using a CASE statement:

(CASE WHEN SALEI.VOPTIONS LIKE '_____%#02%'
      THEN SUBSTRING(SALI.VOPTIONS, 4, PATINDEX('%#02%', SALI.VOPTIONS) - 5)
 END) AS OPTKEY,
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

My guess would be you have a value that returns a null or negative result in the patindex clause.

How I look at problems like this is I try to find the data that is causing the issue, So I would run this query to see what the problem is:

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

then closely examine the results of your query and manually figure out what value would go into the whole substring. Once you know the data issue you have, the solution is usually obvious. My edit will give you what the values being sent to the substring would be and the length of the field which would be a problem if some are not 4 characters in length.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • Yeah I did this. I tried to find the VOPTIONS that would return a value smaller than 5 for this expression : `PATINDEX('%#02%', SALI.VOPTIONS)`. No records... Then I checked all the VOPTIONS manually one by one, but couldn't find anything out of the ordinary. – Volkan Sen Jun 28 '17 at 14:23
  • Well then it might be the substring itself. I wil ledit for somethign to try – HLGEM Jun 28 '17 at 14:27