Do not understand Error message

[Cpreston]

Hi

I have a view that as stopped working.

I have copied the SQL into a query and I get the error message

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.


The code is below. Any ideas where I should be looking to sort the issue. Thanks

SELECT         dbo.Machine.Name, dbo.[148-LastPackNumber](dbo.Machine.MachineID) AS LastPackNumber, dbo.[148-vwMaximumPackNumber].MaximumPackNumber, 
                         dbo.[148-vwMaximumPackNumber].MaximumPackNumber - dbo.[148-LastPackNumber](dbo.Machine.MachineID) AS [Numbers Reserved], dbo.[148-vwDailyPacks].[Daily Average], 
                         (dbo.[148-vwMaximumPackNumber].MaximumPackNumber - dbo.[148-LastPackNumber](dbo.Machine.MachineID)) / CONVERT(int, dbo.[148-vwDailyPacks].[Daily Average]) AS [Days Left], 
                         CASE WHEN Machine.MachineID = 10 THEN 1 ELSE CASE WHEN Machine.MachineID = 15 THEN 2 ELSE CASE WHEN Machine.MachineID = 25 THEN 3 ELSE CASE WHEN Machine.MachineID = 20 THEN 4 ELSE 5
                          END END END END AS Line
FROM            dbo.Machine INNER JOIN
                         dbo.[148-vwDailyPacks] ON dbo.Machine.MachineID = dbo.[148-vwDailyPacks].MachineID INNER JOIN
                         dbo.[148-MachineLookup] ON dbo.Machine.MachineID = dbo.[148-MachineLookup].MachineID LEFT OUTER JOIN
                         dbo.[148-vwMaximumPackNumber] ON dbo.Machine.MachineID = dbo.[148-vwMaximumPackNumber].MachineID
ORDER BY Line

 

[MarkSweetland]

Since it was working and now stopped, my guess is that it is a data issue. I normally encounter the Invalid length error when there is NULL or empty string data in a column where some sort of string function like SUBSTRING or CHARINDEX are used. The naming of the joined tables indicate that they may be views ([148-[red]vw[/red]DailyPacks])and may have some sort of string manipulated column defined. I'd start there...


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Andrzejek]

Not rally related to your error message issue, but...
Aside from keeping these values in a small table in DB so they would not have to be hard-coded in your SQL, wouldn't this logic:

CASE WHEN Machine.MachineID = 10 THEN 1 
  ELSE CASE WHEN Machine.MachineID = 15 THEN 2 
    ELSE CASE WHEN Machine.MachineID = 25 THEN 3 
      ELSE CASE WHEN Machine.MachineID = 20 THEN 4 
        ELSE 5
      END 
    END 
  END 
END AS Line

be the same as this simple way:

CASE Machine.MachineID 
  WHEN 10 THEN 1 
  WHEN 15 THEN 2 
  WHEN 25 THEN 3 
  WHEN 20 THEN 4 END AS Line

---- Andy

There is a great need for a sarcasm font.

 

[gmmastros]

For what it's worth, I think Mark's advice is spot on. Check your other views and also check to see if any of the tables have computed columns while focusing on the use of the substring function.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Thanks for the reply's.

I noticed it was usings views and also a stored procedure which I have tried to change a little. I have tried to change some fo the code and I can get the majority working by omitting the
--IF @MachineID = 60 part of the code. I also tried using --SELECT @Result = LEFT([Output PackRef],@LENG) but this took out the rest of the results for the other machines. So at the moment if I omit the code for machine 60 I get some results, but cannot work out why 60 is causing such an issue.

USE [MAIN]
GO
/****** Object:  UserDefinedFunction [dbo].[148-LastPackNumber]    Script Date: 02/03/2020 11:30:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[148-LastPackNumber] 
(
	@MachineID int
)
RETURNS int
AS
BEGIN
	DECLARE @Result int

	SET @Result = 0
	IF @MachineID = 10
		SELECT @Result = PackageNumber
		FROM vwNXPlaner_LatestFirst_1
	ELSE
		IF @MachineID = 15
			SELECT @Result = PackageNumber
			FROM vwNXPlaner_LatestFirst_2
		ELSE
			IF @MachineID = 20
				SELECT @Result = LEFT([Pack Number],LEN([Pack Number])-2)
				FROM vwSystek_LatestFirst_2
			ELSE
				IF @MachineID = 25
					SELECT @Result = LEFT([Pack Number],LEN([Pack Number])-2)
					FROM vwSystek_LatestFirst_1
				--ELSE
					--IF @MachineID = 60
					--DECLARE @LENG INT = (SELECT LEN([Output PackRef])-2 FROM vwTreatment_LatestFirst_1)
						--SELECT @Result = LEFT([Output PackRef],@LENG)
						--SELECT @Result = LEFT([Output PackRef],LEN([Output PackRef])-2)
						--FROM vwTreatment_LatestFirst_1

	RETURN @Result
END