[SQL Server] inherited an app, Error started the next day |
| inherited an app, Error started the next day Posted: 29 Apr 2013 10:23 AM PDT Hi, First post, I've been using SQL for about 3 years, I just changed jobs and inherited a bunch of code. I have tracked down the problem section of code but haven't worked much with cursors. I could use a hand to solve this bug.------Error-----Msg 536, Level 16, State 5, Line 34Invalid length parameter passed to the SUBSTRING function.The statement has been terminated.Msg 536, Level 16, State 5, Line 34Invalid length parameter passed to the SUBSTRING function.The statement has been terminated..........{INFINITE LOOP}-----Code Snippet------PRINT 'CREATE RECORDS BASED ON MOVEITEMS INFORMATION STOCK'DECLARE @MESSAGE VARCHAR(2000), @ID INT, @DATE DATETIME, @STOCKCODE varchar(50), @DESCRIPTION varchar(2000), @USERID INTCREATE TABLE #TEMP ([ID] INT,[DATE] DATETIME,ITEMID INT,STOCKCODE VARCHAR(50),[DESCRIPTION] VARCHAR(100),USERID INT,QUANTITY FLOAT,DIRECTION VARCHAR(10),LOCATIONID INT)DECLARE MOVEITEMS_CUR CURSORFOR SELECT MOVEITEMSID,MOVEDATE,ITEMID,[DESCRIPTION],USERID FROM MYOB_IMPORT..MOVEITEMS OPEN MOVEITEMS_CURFETCH NEXT FROM MOVEITEMS_CURINTO @ID,@DATE,@STOCKCODE,@DESCRIPTION,@USERIDWHILE @@FETCH_STATUS = 0BEGIN WHILE (LEN(@DESCRIPTION)) > 5 BEGIN INSERT INTO #TEMP ([ID],[DATE],ITEMID,STOCKCODE,[DESCRIPTION],USERID,QUANTITY,DIRECTION,LOCATIONID) SELECT @ID AS [ID], @DATE AS [DATE], @STOCKCODE AS ITEMID, ITEMNUMBER AS STOCKCODE, REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) - 1),CHAR(10),''),'MOVED ',''), @USERID AS USERID, CASE WHEN CHARINDEX('INTO',REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) - 1),CHAR(10),''),'MOVED ','')) = 0 THEN CONVERT(FLOAT,REPLACE(LEFT(@DESCRIPTION,CHARINDEX(' ',@DESCRIPTION) - 1),CHAR(10),'')) ELSE - CONVERT(FLOAT,REPLACE(LEFT(@DESCRIPTION,CHARINDEX(' ',@DESCRIPTION) - 1),CHAR(10),'')) END AS QUANTITY, DIRECTION = CASE WHEN CHARINDEX('INTO',REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) - 1),CHAR(10),''),'MOVED ','')) = 0 THEN 'TO' ELSE 'FROM' END, L.LOCATIONID FROM MYOB_IMPORT..ITEMS I LEFT JOIN MYOB_IMPORT..LOCATIONS L ON LEFT(REVERSE(LEFT(REVERSE(REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) ),CHAR(10),''),'MOVED ','')),CHARINDEX(' ',REVERSE(REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) ),CHAR(10),''),'MOVED ',''))) - 1)),LEN(LEFT(REVERSE(REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) ),CHAR(10),''),'MOVED ','')),CHARINDEX(' ',REVERSE(REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) ),CHAR(10),''),'MOVED ',''))) - 1)) - 1) = L.LOCATIONIDENTIFICATION WHERE @STOCKCODE = ITEMID IF (LEN(@DESCRIPTION) - LEN(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION)))) > 0 BEGIN SELECT @DESCRIPTION = RIGHT(@DESCRIPTION,LEN(@DESCRIPTION) - LEN(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION)))) END ELSE BEGIN BREAK END END FETCH NEXT FROM MOVEITEMS_CUR INTO @ID,@DATE,@STOCKCODE,@DESCRIPTION,@USERIDENDCLOSE MOVEITEMS_CURDEALLOCATE MOVEITEMS_CURGOany assistance appreciated |
| Posted: 29 Apr 2013 07:40 AM PDT i'm stuck!! i'm trying to determine if a customer attended our webinar based on their log in date/time - that's the easy part. but there are many customers who logged in several times, and using a case statement is not working the way i need it to. below is a snippet of my code:case when jh.join_date BETWEEN DATEADD(mi,-45,web.START_DATE_TIME) and web.END_DATE_TIME then 'Attended' else 'Did Not Attend' end and this works if a customer ONLY logged in during the specified times. however, if they logged in during the event AND logged in prior to or after the event, they will be listed multiple times. makes sense so far, except i need either/or. i've tried to re-write my query several times, but i'm not able to exclude those records that fall outside of the specified time.thanks in advance for your help! |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment