asked on

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))

LandyJ

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)"

dkocheno

ASKER

Requests are commented out......If I add that I have to use the group by which is why it errors out

ASKER CERTIFIED SOLUTION

LandyJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LandyJ

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