• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

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))
0
dkocheno
Asked:
dkocheno
  • 3
1 Solution
 
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:
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:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now