This maybe close to what you wanted to achieve
or may give you some ideas how to approach it.
Created Table t1
CREATE TABLE T1 (
SD INTEGER
, ID INTEGER
, RE INTEGER
, PAY INTEGER
, CASH INTEGER
)
and populated with
SD id re pay cash
1 1 0 20 10
2 1 0 30 15
3 2 0 50 12
Converted your TEST procedure
---------------------------------------
CREATE PROCEDURE test
declare
@id int,
@pay int,
@cash int,
@sum int
set @sum = 0
DECLARE sum_cursor Cursor
FOR SELECT
id,
Pay,
Cash
FROM T1
ORDER BY SD
OPEN sum_cursor
FETCH NEXT FROM sum_cursor
INTO
@id,
@pay,
@cash
WHILE @@FETCH_STATUS = 0
BEGIN
select @sum = @sum + (@pay-@cash)
update T1 set RE = @sum
where Id = @Id
FETCH NEXT FROM sum_cursor
INTO
@id,
@pay,
@cash
END
CLOSE sum_cursor
DEALLOCATE sum_cursor
select * from T1
ORDER BY SD
END
GO
---------------------------------------------
To
---------------------------------------------
CREATE PROCEDURE TEST
RETURNS ( SD INTEGER
, ID INTEGER
, RE INTEGER
, PAY INTEGER
, CASH INTEGER)
AS
DECLARE Sum_Id Integer;
DECLARE Sum_RE Integer;
BEGIN
FOR SELECT
Id,
SUM(pay - cash)
FROM T1
GROUP BY iD
INTO
:Sum_Id,
:Sum_RE
DO
BEGIN
UPDATE T1
SET RE = :Sum_RE
WHERE Id = :Sum_Id;
END
FOR
SELECT
SD,
Id,
RE,
Pay,
Cash
FROM T1
INTO
:SD,
:Id,
:RE,
:Pay,
:Cash
DO
SUSPEND;
END
---------------------------------------------
Run a
SELECT * FROM TEST
Which returned the following values
SD id re pay cash
1 1 25 20 10
2 1 25 30 15
3 2 38 50 12
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Farshid Dehghan
Sent: Tuesday, August 21, 2012 1:13 PM
To: [email protected]
Subject: [firebird-support] help-Convert Sql Server to firebird
Please guide me how can I convert the following code.
Sql Server To firebird
CREATE PROCEDURE test
AS
BEGIN
declare @id int, @pay int,@cash int, @sum int set @sum = 0 DECLARE
sum_cursor Cursor FOR SELECT id, Pay,Cash FROM T1 ORDER BY SD OPEN
sum_cursor FETCH NEXT FROM sum_cursor INTO @id, @pay,@cash WHILE
@@FETCH_STATUS= 0 BEGIN select @sum = @sum + (@pay-@cash) update T1 set RE =
@sum where Id = @Id FETCH NEXT FROM sum_cursor INTO @id, @pay,@cash END
CLOSE sum_cursor DEALLOCATE sum_cursor select*from T1 ORDER BY SD END GO
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Resources item on the main
(top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links
