Transform Data Task With Cursor
-
I have a Transform Data Task that uses a cursor to extract data and output it to a text file. When executed, the task output the results of the first pass of the cursor to the file but not any subsequent passes of the cursor.
For example, if my cursor has three items and my select statement is being generated dynamically using the variable being passed by the cursor, only the intial values are output. Does anyone know how to get around this? I tried creating a temp table which works perfectly in QA but the Transform Data task does not like the temp table.
- Script:
- declare @a INT
declare My_Cursor cursor
for select B
from MyTable
- group by B
- open My_Cursor
fetch next from My_Cursor into @A
- while @@FETCH_STATUS = 0
begin
select *
from MyOtherTable
where C = @a
fetch next from My_Cursor into @a
end
- close My_Cursor
deallocate My_Cursor
-
Do you mind posting the real code? I don't see anything wrong with this... and more so I really don't see the need to use a cursor in this case.
You could replace the whole code with this :
Select Whatever from (Select B from dbo.MyTable group by B) dtB inner join dbo.MyOtherTable MOT on dtB.B = MOT.C
What do you need to do exactly?
-
declare @intCCentre INT
declare @fltTotal FLOAT
declare prod_totals cursor
for select fintCostCentreID, sum(fdecHours)
from tblExtract
where fvchProductFamily <> '945'
group by fintCostCentreID
open prod_totals
fetch next from prod_totals into @intCCentre, @fltTotal
while @@FETCH_STATUS = 0
begin
select fintCostCentreID,
fvchProductFamily,
'80000165' as [Schedule],
(sum(fdecHours) / @fltTotal) * 100 as [Value], ftntPeriod, fintYear
from tblExtract
where fintCostCentreID = @intCCentre
and fvchProductFamily <> '945'
group by fintCostCentreID, fvchProductFamily,
ftntPeriod, fintYear
fetch next from prod_totals into @intCCentre, @fltTotal
end
close prod_totals
deallocate prod_totals
The reason for the cursor is because I am trying to sum the hours field i.e. get all rows relating to the particular cost centre and then sum up the hours. The results of this are then output to a text file.
-
I don't know what wrong... but have you tried this instead??
while @@FETCH_STATUS <> -1
-
You should always try avoiding cursors. And here is how u can do it.
select tblExtract.fintCostCentreID, tblExtract.fvchProductFamily,
'80000165' as [Schedule], (sum(tblExtract.fdecHours) / fintCostCentreIDHours.fltTotal) * 100 as [Value], ftntPeriod, fintYear
from tblExtract INNER JOIN (
select fintCostCentreID as SumfintCostCentreID, sum(fdecHours) as fltTotal
from tblExtract
where fvchProductFamily <> '945'
group by fintCostCentreID ) fintCostCentreIDHours On tblExtract.fintCostCentreID = fintCostCentreIDHours.SumfintCostCentreID
where tblExtract.fvchProductFamily <> '945'
group by fintCostCentreID, fvchProductFamily,
ftntPeriod, fintYear
Hope this helps.
Thanks
Sreejith
-
Thanks Sreejith, that worked great!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply