Hey everyone,
I'm running a cursor to concatinate text into a field. I have a work order table with workOrderID as the unique identifyer. I have a comments table that has a foreign key to workOrderID. There is a one-to-many relationship between the tables as a work order can have multiple comments.
I've got a field in the work order table that is supposed to store all the comments for that work order concatenated together. This is the script I am using:
DECLARE @.Comments varchar(8000);
DECLARE @.WorkOrderID VARCHAR(9); -- unique identifier
DECLARE C1 CURSOR
FOR
SELECT c.Comment , w.JobOrder
FROM tblWorkOrderComments AS c
INNER JOIN tblCallIns AS w
ON c.WorkOrder = w.JobOrder
WHERE w.Comments IS NULL;
OPEN C1;
FETCH NEXT FROM C1 INTO @.Comments ,@.WorkOrderID ; -- include the unique identifier into Fetch
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
UPDATE tblCallIns
SET tblCallIns.Comments = ISNULL(tblCallIns.Comments,'') + CHAR(13) + ISNULL(@.Comments,'') -- concatenate comments
WHERE JobOrder = @.WorkOrderID -- filter updated records
END;
FETCH NEXT FROM C1 INTO @.Comments ,@.WorkOrderID ;
END;
CLOSE C1;
DEALLOCATE C1;
It's only pulling the first comment into the comments field and not grabbing any additional comments for the work order.
Can anyone see what's wrong with my code?
Thanks,
Lee
After the UPDATE the w.comments referenced in the cursor is no longer null.
|||Lee,
I think you want to specify FORWARD_ONLY, STATIC, and KEYSET in your CURSOR declaration.
Otherwise the CURSOR is DYNAMIC, and working in light of your UPDATE actions.
http://msdn2.microsoft.com/en-us/library/ms180169.aspx
Hope that helps,
Dan
|||hi try this..DECLARE @.tblWorkOrderComments TABLE (WorkOrder INT, Comment VARCHAR(800))
DECLARE @.tblCallIns TABLE (JobOrder INT, Comments VARCHAR(800))
INSERT INTO @.tblWorkOrderComments VALUES (1, 'the')
INSERT INTO @.tblWorkOrderComments VALUES (1, 'quick')
INSERT INTO @.tblWorkOrderComments VALUES (1, 'brown')
INSERT INTO @.tblWorkOrderComments VALUES (2, 'fox')
INSERT INTO @.tblWorkOrderComments VALUES (2, 'jumps')
INSERT INTO @.tblCallIns VALUES (1, NULL)
INSERT INTO @.tblCallIns VALUES (2, NULL)
INSERT INTO @.tblCallIns VALUES (3, NULL)
INSERT INTO @.tblCallIns VALUES (4, NULL)
DECLARE @.Comments varchar(8000);
DECLARE @.WorkOrderID VARCHAR(9); -- unique identifier
DECLARE C1 CURSOR FOR
SELECT DISTINCT w.JobOrder -- get only the joborder with comments
FROM @.tblWorkOrderComments AS c
INNER JOIN @.tblCallIns AS w
ON c.WorkOrder = w.JobOrder
WHERE w.Comments IS NULL;
OPEN C1;
FETCH NEXT FROM C1 INTO @.WorkOrderID ; -- include the unique identifier into Fetch
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--IF (@.@.FETCH_STATUS <> -2)
--BEGIN
SET @.Comments = ''
SELECT @.Comments = @.Comments + CHAR(13) + ISNULL(Comment,'') -- this will concatenate all the comments per workorder
FROM @.tblWorkOrderComments
WHERE WorkOrder = @.WorkOrderID
ORDER BY
WorkOrder
UPDATE @.tblCallIns
SET Comments = @.Comments -- concatenate comments
WHERE JobOrder = @.WorkOrderID -- filter updated records
--END;
FETCH NEXT FROM C1 INTO @.WorkOrderID ;
END;
CLOSE C1;
DEALLOCATE C1;
SELECT * FROM @.tblWorkOrderComments
SELECT * FROM @.tblCallInssql
