01-27-2016 07:50 AM
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
Solved! Go to Solution.
01-30-2016 07:58 AM
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).
01-30-2016 07:58 AM
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).
02-02-2016 03:35 PM
Thanks Jameson, I will be able to get on the customers system tomorrow so will look to try what you have suggested,
Sarah
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: