Skip to Content
0

query error

Apr 05, 2017 at 11:17 AM

98

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Jitin Chawla
Apr 05, 2017 at 11:27 AM
0

Hi,

Where are you executing this query?

I checked in MSSQL, no error appeared.

Regards,

Jitin

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Jitin,

In my DB its showing an error.

The error is in user code i think only sales 8 not working other user codes working.

can u please help us

sales.jpg (65.8 kB)
0
DIEGO LOTHER Apr 05, 2017 at 12:11 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Apr 05, 2017 at 12:13 PM
0

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
Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Apr 05, 2017 at 12:35 PM
0

Hi,

No error while running above query in query generator.

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded