Skip to Content

query error

Hi Experts

while executing the below query am getting an error.

"Invalid length parameter passed to the LEFT or SUBSTRING function".

SELECT bp.CardCode AS 'BP Code', bp.CardName AS 'BP Name', 'N/a' AS 'Opp ID', '' AS 'Customer Status', 'N/a' AS 'Opp Owner', 0 AS 'Opp Value', Cast(act.ClgCode AS NVARCHAR) AS 'Act ID', usr.U_NAME AS 'Act Owner', act.Priority AS Priority, act.Details AS 'Activity Notes', act.Recontact AS 'Act Date', Right('0'+Left(act.BeginTime,(Len(act.BeginTime)-2))+':' +Right(act.BeginTime,2),5) AS 'Act Time', cont.[E_MailL] AS 'Contact Email' FROM OSLP sal, OUSR usr, OCRD bp, OCLG act, OCPR cont WHERE act.SlpCode = sal.SlpCode AND sal.U_BOY_50_BRKO = usr.USER_CODE AND act.CardCode = bp.CardCode AND act.CntctCode = cont.CntctCode AND act.Closed = 'N' AND usr.USER_CODE = 'sales8' AND NOT EXISTS (SELECT opp3.OpprId FROM OOPR opp3 WHERE opp3.OpprId = act.parentId AND act.parentType = 97) AND act.Recontact > '2012-05-31' ORDER BY [Act Date], Priority DESC, [Opp Value] DESC

Thanks

Vinoth

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Apr 05, 2017 at 11:27 AM

    Hi,

    Where are you executing this query?

    I checked in MSSQL, no error appeared.

    Regards,

    Jitin

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 05, 2017 at 12:11 PM

    Hi Vinoth,

    Probably you received this error because you have the value 0 in some registers on the field act.BeginTime.

    Try this query:

    SELECT 
    	bp.CardCode AS 'BP Code', 
    	bp.CardName AS 'BP Name', 
    	'N/a' AS 'Opp ID', 
    	'' AS 'Customer Status', 
    	'N/a' AS 'Opp Owner', 
    	0 AS 'Opp Value', 
    	Cast(act.ClgCode AS NVARCHAR) AS 'Act ID', 
    	usr.U_NAME AS 'Act Owner', 
    	act.Priority AS Priority, 
    	act.Details AS 'Activity Notes', 
    	act.Recontact AS 'Act Date', 
    	SUBSTRING(REPLICATE(0, 4 - Len(act.BeginTime)) + CAST(act.BeginTime AS NVARCHAR), 0, 3) + ':' +
    	SUBSTRING(REPLICATE(0, 4 - Len(act.BeginTime)) + CAST(act.BeginTime AS NVARCHAR), 3, 2),
    	cont.[E_MailL] AS 'Contact Email' 
    FROM 
    	OSLP sal, 
    	OUSR usr, 
    	OCRD bp, 
    	OCLG act, 
    	OCPR cont 
    WHERE 
    	act.SlpCode = sal.SlpCode 
    	AND sal.U_BOY_50_BRKO = usr.USER_CODE 
    	AND act.CardCode = bp.CardCode 
    	AND act.CntctCode = cont.CntctCode 
    	AND act.Closed = 'N' 
    	AND usr.USER_CODE = 'sales8' 
    	AND NOT EXISTS (SELECT opp3.OpprId FROM OOPR opp3 WHERE opp3.OpprId = act.parentId AND act.parentType = 97) AND act.Recontact > '2012-05-31' ORDER BY [Act Date], Priority DESC, [Opp Value] DESC
    

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 05, 2017 at 12:13 PM

    Please run your query again, but like below. See what the value of act.BeginTime is, and you may find out why the error is thrown only for user8:

    SELECT bp.CardCode AS 'BP Code'
         , bp.CardName AS 'BP Name'
         , 'N/a' AS 'Opp ID'
         , '' AS 'Customer Status'
         , 'N/a' AS 'Opp Owner'
         , 0 AS 'Opp Value'
         , Cast(act.ClgCode AS NVARCHAR) AS 'Act ID'
         , usr.U_NAME AS 'Act Owner'
         , act.Priority AS Priority
         , act.Details AS 'Activity Notes'
         , act.Recontact AS 'Act Date'
         , act.BeginTime
       /*, Right('0'+Left(act.BeginTime,(Len(act.BeginTime)-2))+':' +Right(act.BeginTime,2),5) AS 'Act Time'*/
         , cont.[E_MailL] AS 'Contact Email' 
    FROM OSLP sal, OUSR usr, OCRD bp, OCLG act, OCPR cont 
    WHERE act.SlpCode = sal.SlpCode 
      AND sal.U_BOY_50_BRKO = usr.USER_CODE 
      AND act.CardCode = bp.CardCode 
      AND act.CntctCode = cont.CntctCode 
      AND act.Closed = 'N' 
      AND usr.USER_CODE = 'sales8' 
      AND NOT EXISTS (SELECT opp3.OpprId 
                      FROM OOPR opp3 
      WHERE opp3.OpprId = act.parentId 
        AND act.parentType = 97) 
        AND act.Recontact > '2012-05-31' 
    ORDER BY [Act Date], Priority DESC, [Opp Value] DESC
    Add comment
    10|10000 characters needed characters exceeded

  • Apr 05, 2017 at 12:35 PM

    Hi,

    No error while running above query in query generator.

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded