Hi I built a cursor to update several fields. I tried the SQL sentence that I set as sSQL1 with a value and it worked, but when I put it into the cursor I'm getting an error that I assume is on the following part:
''('' + [Division] + '') - ('' + [Category] + '') - ('' + [ProductFamily] + '')''
as seems that is not formatting the field + text as needed.
When I tried the following it worked :
UPDATE table1 SET [PF - (Product A) - (Subproduct B) - (Subproduct C)] = lic
FROM (select mssendcustomertpid, sum(lic) as lic from table2 where '(' + Division + ') - (' + Category+ ') - (' + ProductFamily+ ')' ='PF - (Product A) - (Subproduct B) - (Subproduct C)' group by customertpid)
B
INNER JOIN table1 A ON B.customertpid = A.customertpid
This is the cursor:
DECLARE @prod nvarchar(100), @sSQL NVARCHAR(500), @sSQL1 NVARCHAR(1000)
DECLARE cr cursor local FAST_FORWARD for
SELECT DISTINCT product
FROM table3 order by 1
open cr
fetch next from cr into @prod
while @@fetch_status=0
begin
SET @sSQL1= 'UPDATE table1 SET [PF - ' + @prod + '] = lic
FROM (select mssendcustomertpid, sum(lic) as lic from table2 where ''('' + [Division] + '') - ('' + [Category] + '') - ('' + [ProductFamily] + '')'' =' + @prod + ' group by customertpid)
B
INNER JOIN table1 A ON B.customertpid = A.customertpid)'
EXECute(@sSQL1)
end
close cr
deallocate cr
Any help?
Thanks
Ezequiel