Invalid length parameter passed to the substring function.

the following query runs fine with the code commented out of the group by, but errors with it - how can I get around it?

select      DayID = convert(numeric,replace(cast(year(EventTime) as varchar(4))+ '/' + right('0' + cast(month(EventTime) as
            varchar(2)), 2) + '/' + right('0' + cast(day(EventTime) as varchar(2)), 2) ,'/','')),
            CorpCode,
            CustomerNumber,
            UserID,
            Application,
            RequestedURI,
            ImageName = substring(WE.RequestedURI,patindex('%[_]%',WE.RequestedURI)+1, CHARINDEX('_',WE.RequestedURI, CHARINDEX('_',WE.RequestedURI)+1)-1-patindex('%[_]%',WE.RequestedURI))
--            Requests = count(*)
      from      TDTRANSFER..WebEventsBackup WE with (nolock)
      join URLS with (nolock)
      on      WE.RequestedURI like URL
      where WE.ReferrerURI like '%ene_categories%' or WE.ReferrerURI like '%ene_results%'
      group by convert(numeric,replace(cast(year(EventTime) as varchar(4))+ '/' + right('0' + cast(month(EventTime) as
            varchar(2)), 2) + '/' + right('0' + cast(day(EventTime) as varchar(2)), 2) ,'/','')), CorpCode,
            CustomerNumber, UserID, Application,RequestedURI--, substring(WE.RequestedURI,patindex('%[_]%',WE.RequestedURI)+1, CHARINDEX('_',WE.RequestedURI, CHARINDEX('_',WE.RequestedURI)+1)-1-patindex('%[_]%',WE.RequestedURI))
dkochenoAsked:
Who is Participating?
 
LandyJCommented:
One of the values in WE.RequestedURI is invalid.  My guess is that it is sending a negative number for this function result:
CHARINDEX('_',WE.RequestedURI, CHARINDEX('_',WE.RequestedURI)+1)-1-patindex('%[_]%',WE.RequestedURI)
0
 
LandyJCommented:
What error are you getting?  

You don't have a comma before Requests = count(*).  Also, you should count one of the fields in the Group By clause.  
Try  ", Request = count(UserID)"



0
 
dkochenoAuthor Commented:
Requests are commented out......If I add that I have to use the group by which is why it errors out
0
 
LandyJCommented:
To test that, sun this:

select  RequestedURI
     from     TDTRANSFER..WebEventsBackup WE with (nolock)
     join URLS with (nolock)
     on     WE.RequestedURI like URL
     where WE.ReferrerURI like '%ene_categories%' or WE.ReferrerURI like '%ene_results%'
     AND CHARINDEX('_',WE.RequestedURI, CHARINDEX('_',WE.RequestedURI)+1)-1-patindex('%[_]%',WE.RequestedURI) < 1

hth,
Landy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.