How to use Update cursor
-
CREATE PROCEDURE BrzaKartica( @xOk char(2), @xOrg char(2), @xSifra Char(7) ) AS
--================================
-- OBRISI PROTOCNU TABELU karticka
--================================
DELETE FROM Karticka ;
--=================================================
-- POPUNI JE NANOVO ZA ODABRANE PARAMETRE
--=================================================
INSERT INTO Karticka
SELECT OK, ORG, VP, BRDOK, DATDOK , VREME, SIFRA,
CASE
WHEN ( VP IN ('20', '79', '82' ) ) THEN KOL
ELSE 0
END as "'KOL1",
CASE
WHEN ( VP not IN ('20', '79', '82') ) THEN KOL1
ELSE 0
END as "KOL2",
0,
CENA,
0,
0,
0,
'Dokument '
FROm PS620
WHERE ( OK = @xOk AND ORG = @xOrg AND SIFRA = @xSifra ) ;
--===============================
-- DEKLARISI LOKALE
--===============================
DECLARE @M_Kol1 decimal(19,3)
DECLARE @M_Kol2 decimal(19,3)
DECLARE @M_Kol3 decimal(19,3)
DECLARE @M_Cena decimal(19,3)
DECLARE @M_Vrs1 decimal(19,2)
DECLARE @M_Vrs2 decimal(19,2)
DECLARE @M_Vrs3 decimal(19,2)
DECLARE @M_Vp char(2)
--===============================
-- INICIJALIZUJ LOKALE
--===============================
SET @M_Kol1 = 0
SET @M_Kol2 = 0
SET @M_Kol3 = 0
SET @M_Vrs1 = 0
SET @M_Vrs2 = 0
SET @M_Vrs3 = 0
SET @M_Cena = 0
--===============================
-- OTVORI I PROCITAJ KURSOR
--===============================
DECLARE Kure SCROLL CURSOR FOR
SELECT VP, KOL1, KOL2, CENA
FROM Karticka
ORDER BY DATDOK, VREME
FOR UPDATE OF KOL1, KOl2, KOL3, VRS1, VRS2, VRS3
OPEN Kure
FETCH NEXT FROM Kure INTO @M_Vp, @M_Kol1, @M_Kol2, @M_Cena
WHILE ( @@FETCH_STATUS <> 0 )
BEGIN
IF @M_VP IN ( '20', '79', '82 ' ) -- Samo KOL1 ima vrednsot , ostali su nule
BEGIN
SET @M_KOL3 = @M_Kol3 + @M_Kol1
SET @M_Vrs1 = ROUND( @M_Kol1 + @M_Cena,2)
SET @M_Vrs3 = @M_Vrs3 + @M_Vrs1
UPDATE Karticka SET
KOL2 = 0,
KOL3 = @M_Kol3,
VRS1 = @M_VRS1,
VRS2 = 0,
VRS3 = @M_VRS3,
Cena = 0.9
WHERE Current of Kure;
END
ELSE
IF @M_VP IN ( '50', '60', '80', '81' ) -- Samo KOL1 ima vrednsot , ostali su nule
BEGIN
SET @M_KOL3 = @M_Kol3 - @M_Kol2
SET @M_Vrs2 = ROUND( @M_Kol2 + @M_Cena,2)
SET @M_Vrs3 = @M_Vrs3 - @M_Vrs2
UPDATE Karticka SET
KOL1 = 0,
KOL3 = @M_Kol3 ,
VRS1 = 0,
VRS2 = @M_VRS2,
VRS3 = @M_VRS3 ,
Cena = 0.9
WHERE Current of Kure;
END
FETCH NEXT FROM Kure INTO @M_Vp, @M_Kol1, @M_Kol2, @M_Cena
Print 'Zojko'
END
CLOSE Kure
DEALLOCATE Kure
--=================================================
-- JOS PRIREDI STA SE TREBA VIDETI U Grid-u
--=================================================
SELECT * FROM Karticka
GO
When I exec this I got error :
"Server: Msg 16957, Level 16, State 4, Procedure BrzaKartica, Line 65
FOR UPDATE cannot be specified on a READ ONLY cursor."
Thank Zoran
-
Interesting one - I haven't seen update cursors before, but noticed this article:
http://www.dbforums.com/archive/index.php/t-1103811.html
which indicates that perhaps the table needs a unique index on it for this to work...??
On a slightly different tack, could you not do away with the cursor here and just use update statements?
i.e.
UPDATE
Karticka
SET KOL2 = 0,
KOL3 = KOL3 + KOl1,
VRS1 = ROUND( KOL1 + VRS3, 2),
VRS2 = 0,
VRS3 = VRS3 + VRS1,
CENA = 0.9
WHERE KOL1 IN ( '20', '79', '82 ' )
UPDATE
Karticka
SET KOL1 = 0,
KOL3 = KOL3 - KOL2,
VRS1 = 0,
VRS2 = ROUND( KOL2 + VRS3, 2),
VRS3 = VRS3 - VRS2,
CENA = 0.9
WHERE KOL1 IN ( '50', '60', '80', '81' )
JT
-
First I want to thank to JT. It work "perhaps the table needs a unique index on it for this to work...??".
Second, my English is not good, and I do not understand "On a slightly different tack"
About "could you not do away with the cursor here and just use update statements" my rpoblem is :Have a table with Type and Quantiy field like (about 10,000 rec.)
TYPE QUANTITY
(i) 1 10
(ii) 5 3
(iii) 2 20
(iv) 4 5
(v) 3 0
and try to make table like
TYPE INPUT OUTPUT SALDO
(of changes) (quantity) (quantity) (quantity)
======================================================
(i) 1 1010
(ii) 5 3 7
(iii) 2 20 -13
(iv) 4 5-7
(v) 3 -7
---------------------------------------------------
15 23-7
My APP have a 10 users and I try to speed up it.
I read that StoredProc is faster than Dinamic query
I am sorry, I am not SQL expert, but I try. Thanks again to JT.
-
What tell there that 10 is input but 3 is output?
_____________
Code for TallyGenerator -
For example value of field "TYPE" IS:
FOR INPUT = { 1,4 },
FOR OUTPUT = { 5,2 },
FOR NOCHANGE (but make record) = { 3 }
Thank Serqiy
-
In response to post by Zoran Kovacevic on 3/2/2007 2:56:00 PM CST:
<Have a table with Type and Quantity field like (about 10,000 rec.)and try to make table like
TYPE INPUT OUTPUT SALDO (of changes) (quantity) (quantity) (quantity)
>
A single SQL Statement can perform this transformation without using a cursor. There are two techniques used - Pivot and Running Total.
Create table EventOperations
( EventOperationId integer not null
, EventOperationAction varchar(255) not null
, constraint EventOperations_P primary key (EventOperationId)
, constraint EventOperations_C_EventOperationAction
check ( EventOperationAction in ('Input','Output' , 'None' ) )
)
Create table Events
( EventId varchar(255) not null
, EventOperationId integer not null
, EventQty integer not null
, constraint Events_P primary key (EventId)
, constraint EventOperations_F_Events foreign key (EventOperationId) references EventOperations
)
insert into EventOperations
(EventOperationId , EventOperationAction)
select 1 , 'Input' union all
select 2 , 'Output' union all
select 3 , 'None' union all
select 4 , 'Input' union all
select 5 , 'Output'
insert into Events
( EventId , EventOperationId , EventQty 
select 1, 1, 10 union all
select 2, 5, 3 union all
select 3, 2, 20 union all
select 4, 4, 5 union all
select 5, 3, 0
select Events.EventId
, Events.EventOperationId
, EventOperations.EventOperationAction
-- PIVOT
, CASE WHEN EventOperations.EventOperationAction = 'Input' then EventQty else null end as Input
, CASE WHEN EventOperations.EventOperationAction = 'Output' then EventQty else null end as Output
, SALDO
from EventOperations
join Events
on Events.EventOperationId = EventOperations.EventOperationId
join ( -- Running Total
select Events.EventId
, sum( CASE EventOperations.EventOperationAction
WHEN 'Input' then PriorEvents.EventQty
WHEN 'Output' then -1 * PriorEvents.EventQty
else 0 END
 
as SALDOfrom Events
join Events as PriorEvents
on PriorEvents.EventId <= Events.EventId
join EventOperations
on EventOperations.EventOperationId = PriorEvents.EventOperationId
group by Events.EventId
 
as EventSALDOson EventSALDOs.EventId = Events.EventId
The result set is:
EventId EOId Action Input Output SALDO
----------- ----------- ------ ----------- ----------- -----------
1 1 Input 10 NULL 10
2 5 Output NULL 3 7
3 2 Output NULL 20 -13
4 4 Input 5 NULL -8
5 3 None NULL NULL -8
SQL = Scarcely Qualifies as a Language
-
Thanks to Carl Federl. You get a point . It is true 8+5 =13, I am sorry by mistake. I will try to understand your code soon (Pivot and Running Total) and try to learn new syntax(to me). I never to use syntax like "sum( CASE EventOperations.EventOperationAction
WHEN 'Input' then PriorEvents.EventQty
WHEN 'Output' then -1 * PriorEvents.EventQty
else 0 END
as SALDO"Thanks again.
-
Joe Celko's SQL for Smarties: Advanced SQL Programming is a must read !!!
SQL = Scarcely Qualifies as a Language
-
Thanks to Carl Federl. I try your code and it work. I want to ask you can I do same thing only with one table (without EventOperations) becouse I know that 1 or 4 is INPUT, 2 or 5 is OUTPT and 3 is NONE it is CONST. and I know that there is no any other TYPE value. Why another table ? I know the value (1,2,3,4,5) and I know what it meaning (operation) , this things have to know only SQL-code and I, no users. I want to resolve only this case, not any one similar becouse to me is to easy to learn on single case, if I understand all stori (your code, except syntax for now). I work with SQL Server 2ooo
Thanks once again. I will try to find Joe Celko's. My only book for now is SQL Server 2000 - Developer's Guide by Michael Otey, Paul Cont and some books in my language
-
Zoran, are you a part of the system?
When they will install the system will they install you next to the server?
If yes then it's OK to use you knowledge as part of the system.
If not you shoul forget words "I know". Any knowledge used in database must be stored in tables and retrieved from there when needed by queries.
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply