cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
668
Views
0
Helpful
2
Replies

Database Error showing 537 message Invalid length parameter passed to the LEFT or SUBSTRING

sarbarnes
Enthusiast
Enthusiast

I have created a report which I have just imported into a customer site

This is the Substring i have created to separate out data that is being shown in the Variables within the Termination Call Detail report

TCD.Variable2 as MIDandStatus,

TCD.Variable3 as BrandandPlatform ,

TCD.Variable4 as RoutingReason,

SUBSTRING(TCD.Variable2,1,CHARINDEX(':',TCD.Variable2)-1) AS MID,

SUBSTRING(TCD.Variable2,CHARINDEX(':',TCD.Variable2)+1,CHARINDEX(':',TCD.Variable2,CHARINDEX(':',TCD.Variable2)+1)-1) AS Status,

SUBSTRING(TCD.Variable3,1,CHARINDEX(':',TCD.Variable3)-1) AS Brand,

SUBSTRING(TCD.Variable3,CHARINDEX(':',TCD.Variable3)+1,CHARINDEX(':',TCD.Variable3,CHARINDEX(':',TCD.Variable3)+1)-1) AS Platform,

SUBSTRING(TCD.Variable4,1,CHARINDEX(':',TCD.Variable4)-1) AS RoutedTo,

SUBSTRING(TCD.Variable4,CHARINDEX(':',TCD.Variable4)+1,CHARINDEX(':',TCD.Variable4,CHARINDEX(':',TCD.Variable4)+1)-1) AS RoutedReason,

When I have now run the report in live,  I get the database error. I did not have this issue in the Lab!

Can anyone help?

Thanks Sarah

1 Accepted Solution

Accepted Solutions

Jameson Gagnepain
Rising star
Rising star

Sarah,

It sounds like one or more of your CHARINDEX calls is returning a NULL value. This means at least one of the rows you are reporting on does not have ':' in Variable2, 3, or 4. It would be good a good idea to wrap all of your CHARINDEX calls with ISNULL. Here's the Microsoft doc on it -> ISNULL (Transact-SQL).

-Jameson

View solution in original post

2 Replies 2

Jameson Gagnepain
Rising star
Rising star

Sarah,

It sounds like one or more of your CHARINDEX calls is returning a NULL value. This means at least one of the rows you are reporting on does not have ':' in Variable2, 3, or 4. It would be good a good idea to wrap all of your CHARINDEX calls with ISNULL. Here's the Microsoft doc on it -> ISNULL (Transact-SQL).

-Jameson

Thanks Jameson, I will be able to get on the customers system tomorrow so will look to try what you have suggested,

Sarah

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community:

Recognize Your Peers