March 18, 2011 at 3:14 am
Hi,
when I am running below SQL I am geting the error msg
"Invalid length parameter passed to the LEFT or SUBSTRING function"
SELECT user_name1,Report_name ReportName,Name,COUNT(ReportID) Run_Count
FROM(SELECT a.Name,b.Status,b.TimeProcessing,b.TimeDataRetrieval,
b.TimeRendering,b.TimeStart,b.TimeEnd,c.UserName,
SUBSTRING(a.Name,CHARINDEX('_',a.Name,1)+1,CHARINDEX('_',a.Name,(CHARINDEX('_',a.Name,1)+1))-CHARINDEX('_',a.Name,1)-1)user_name1,
b.ReportID,n.rpt_name Report_name
FROM Catalog a
LEFT OUTER JOIN ExecutionLog b ON a.ItemID = b.ReportID
LEFT OUTER JOIN Users c ON a.CreatedByID = c.UserID
LEFT OUTER JOIN [IN_NETFORUM_DEV].dbo.md_report n
ON a.Name = SUBSTRING(n.rpt_file_name,1,LEN(n.rpt_file_name)-4) COLLATE SQL_Latin1_General_CP1_CI_AI
WHERE TimeStart >= CONVERT(DATETIME,'08-01-2010',102)
AND TimeEnd <= CONVERT(DATETIME,'03-31-2011',102)
AND n.rpt_file_name LIKE '%.%' and Len(a.Name) >= 4
) rptdtl
GROUP BY user_name1,Report_name,Name
HAVING COUNT(ReportID) > 0
ORDER BY 3
I am getting the error from this line
SUBSTRING(a.Name,CHARINDEX('_',a.Name,1)+1,CHARINDEX('_',a.Name,(CHARINDEX('_',a.Name,1)+1))-CHARINDEX('_',a.Name,1)-1)user_name1
OR
this line
WHERE TimeStart >= CONVERT(DATETIME,'08-01-2010',102)
AND TimeEnd <= CONVERT(DATETIME,'03-31-2011',102)
Data is huge in the above tables so I am not find out which row would
be the problem.
So request you to please varify my above sql and help me out from my problem.
Regards,
Kiran R
March 18, 2011 at 3:42 am
You aren't really giving anybody much to go on.
Even a bit of sample data would help, along with what you are trying to achieve and some expected results.
The error message you are getting shows the problem is with the SUBSTRING statement.
I'm guessing you are trying to split up a user name, and it's delimited using an "_".
Try taking just the substring part of the select on its own and seeing if it works with one bit of sample data in a variable instead of from the tables, e.g.
declare @name varchar(50) = 'sample_name'
select SUBSTRING(@name,CHARINDEX('_',@name,1)+1,CHARINDEX('_',@name,(CHARINDEX('_',@name,1)+1))-CHARINDEX('_',@name,1)-1)user_name1
This may be what you meant, but without samples and knowing what you are trying to achieve, nodoby will be able to give you a tested solution.
select SUBSTRING(@name,CHARINDEX('_',@name,1)+1,CHARINDEX('_',@name,(CHARINDEX('_',@name,1)+1))+CHARINDEX('_',@name,1))
March 18, 2011 at 4:04 am
Ian is correct that it's tough to decipher your issue from this end with the description, however...
Your code needs to confirm there is a second underscore in the string before you try to decompose the string. If that code runs into a string without two underscores in it, I'd expect to see the error you got. Wrap the structure in a case statement that does the double charindex check and confirm it's <> 0 before you do the decomposition.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply