My Cursor don´t UPDATE

Question

Post reply

My Cursor don´t UPDATE

luissantos

SSCertifiable

Points: 7391
More actions
November 30, 2014 at 2:27 pm

#292406

Hello comunity
I have build this cursor, but the same one don´t make the Update to my table ft2.
here is my Cursor code:
DECLARE cur CURSOR LOCAL FORWARD_ONLY FOR
SELECT ft.ftstamp, ft.ndoc, ft.fno, ft.nmdoc, Sum(fi.ecusto*qtt)
FROM fi (nolock) inner join ft (nolock) on fi.ftstamp = ft.ftstamp
INNER JOIN ft2 (nolock) ON ft2stamp = ft.ftstamp
WHERE YEAR(fi.rdata)=2014 AND MONTH(fi.rdata)=8
AND fi.stns <> 1 and fi.epv<> 0
and ft.anulado = 0 and ft2.ftaxamt_b = 0
AND ft.tipodoc IN (1,2)
GROUP BY ft.ftstamp,ft.ndoc,ft.fno,ft.nmdoc
Order by ft.ndoc, ft.fno asc
DECLARE @Stamp varchar(25)
DECLARE @ndoc numeric(3,0)
DECLARE @nrdoc numeric(10,0)
DECLARE @documento varchar(20)
DECLARE @ecusto NUMERIC(19,6)
OPEN cur
FETCH NEXT FROM cur INTO @STAMP, @ndoc, @nrdoc, @documento , @ecusto
WHILE @@FETCH_STATUS = 0
BEGIN
Update ft2
set ft2.ftaxamt_a = @ecusto
from ft2
INNER JOIN ft ON ft2stamp = ft.ftstamp
where ft.ndoc = @ndoc and ft.fno = @nrdoc
AND ft.nmdoc = @documento
and ft.ftstamp = @STAMP AND ft2stamp = @STAMP
--Print 'Valor Custo mercadoria ' + Rtrim(@documento) +cast(@ndoc as varchar) + ' nº : '+Cast(@nrdoc as varchar) + ' Valor de : ' +Cast(@ecusto as varchar) + ' ' + @STAMP
FETCH NEXT FROM cur INTO @STAMP, @ndoc, @nrdoc, @documento, @ecusto
END
CLOSE cur
DEALLOCATE cur
Could someone help me
Many thanks
Luis santos

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

luissantos SSCertifiable Points: 7391 More actions · Answer 1

Hello comunity

I found the problem and solve the situation because i not write the correct name field to Update.

the correct name is eftaxam_a and not ftaxamt_a.

my apologies.

Best regards,

Luis Santos

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

luissantos (11/30/2014)
Hello comunity
I found the problem and solve the situation because i not write the correct name field to Update.
the correct name is eftaxam_a and not ftaxamt_a.
my apologies.
Best regards,
Luis Santos

No apologies necessary. Sometimes posting helps you find your own problem.

I had started to look at your problem and had other events force me to set it aside. I'd have to do a deeper dive to provide a non-RBAR solution but my first take on this is that you don't need a cursor to do this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

Further on Jeff's reply, here is a sketch / pseudo code showing how a cursor-less equivalent could look like. Note: this may NOT work as it is, some tweaking with table names and aliases is required!

😎

;WITH BASE_SET AS

(

SELECT

ft.ftstamp

, ft.ndoc

, ft.fno

, ft.nmdoc

, Sum(fi.ecusto*qtt) AS SUM_ECUSTO

FROM fi

INNER JOIN ft

ON fi.ftstamp = ft.ftstamp

INNER JOIN ft2

ON ft2stamp = ft.ftstamp

WHERE YEAR(fi.rdata) = 2014

AND MONTH(fi.rdata) = 8

AND fi.stns <> 1

AND fi.epv <> 0

AND ft.anulado = 0

AND ft2.ftaxamt_b = 0

AND ft.tipodoc IN (1,2)

GROUP BY ft.ftstamp

,ft.ndoc

,ft.fno

,ft.nmdoc

)

UPDATE ft2

SET ft2.eftaxam_a = BS.SUM_ECUSTO

FROM ft2

INNER JOIN BASE_SET BS

ON ft2stamp = BS.ftstamp

WHERE ft2.ftaxamt_b = 0;

One quick question, why the nolock hints?