Thread: conversi ms-sql7 vs postgresql 7.3

conversi ms-sql7 vs postgresql 7.3

From
"betty"
Date:
Hi..

I have table xx:
id  debet       credit           balance
1  1000        0                   0
2   2000       0                   0
3         0       2500             0
4         0         100             0

command in ms-sql 7 can use calculate field (column) balance from id=1 to
id=4:
"update xx set bal=balance=bal+debet-credit"
result:
id  debet       credit           balance
1  1000        0                  1000
2   2000       0                   3000
3         0       2500             500
4         0         100             400

How command sql can use in psotgresql 7.3?

thank's a lot
betty.





Re: conversi ms-sql7 vs postgresql 7.3

From
Christoph Haller
Date:
>
> I have table xx:
> id  debet       credit           balance
> 1  1000        0                   0
> 2   2000       0                   0
> 3         0       2500             0
> 4         0         100             0
>
> command in ms-sql 7 can use calculate field (column) balance from id=1
to
> id=4:
> "update xx set bal=balance=bal+debet-credit"
> result:
> id  debet       credit           balance
> 1  1000        0                  1000
> 2   2000       0                   3000
> 3         0       2500             500
> 4         0         100             400
>
> How command sql can use in psotgresql 7.3?
>
Try
UPDATE xx SET balance=balance+debet-credit ;

Regards, Christoph




Re: conversi ms-sql7 vs postgresql 7.3

From
Richard Huxton
Date:
On Thursday 06 Feb 2003 10:10 am, betty wrote:
> Hi..
>
> I have table xx:
> id  debet       credit           balance
> 1  1000        0                   0
> 2   2000       0                   0
> 3         0       2500             0
> 4         0         100             0
>
> command in ms-sql 7 can use calculate field (column) balance from id=1 to
> id=4:
> "update xx set bal=balance=bal+debet-credit"
> result:
> id  debet       credit           balance
> 1  1000        0                  1000
> 2   2000       0                   3000
> 3         0       2500             500
> 4         0         100             400
>
> How command sql can use in psotgresql 7.3?

Three options:
1. Write a function to update the records procedurally (see the plpgsql
section of the manuals). I'd probably use a cursor for this.

2. Write a trigger to keep entries up to date as you insert/delete/update
entries (basically, same as above but keeps things up to date)

3. Use a sub-query
richardh=> SELECT * FROM bank;id | credit | debit | balance
----+--------+-------+--------- 1 |   1000 |     0 |       0 2 |      0 |   250 |       0 3 |   2000 |     0 |       0
(3 rows)

richardh=> UPDATE bank SET balance = (SELECT sum(credit)-sum(debit) AS newbal
FROM bank b2 WHERE b2.id<=bank.id);
UPDATE 3
richardh=> SELECT * FROM bank;id | credit | debit | balance
----+--------+-------+--------- 1 |   1000 |     0 |    1000 2 |      0 |   250 |     750 3 |   2000 |     0 |    2750
(3 rows)

Note that for lots of rows, this may be slow. You might want to rewrite it in
the form b2.balance+b2.credit-b2.debit where b2.id=bank.id-1 but this
requires "id" to not have any gaps in the sequence and also needs special
logic to handle id=1 (or whatever the smallest "id" is).

HTH

--  Richard Huxton


Re: conversi ms-sql7 vs postgresql 7.3

From
"betty"
Date:
hi Christoph,

your command :
"UPDATE xx SET balance=balance+debet-credit "
result calculate balance for each row, ex:id  debet       credit           balance1  1000        0
10002  2000       0                   20003         0       2500             -25004         0         100
-100

not result calculate balance, ex:
id  debet       credit           balance1  1000        0                  10002   2000       0                   30003
      0       2500             5004         0         100             400
 

regards
betty

"Christoph Haller" <ch@rodos.fzk.de> wrote in message
news:3E438396.2983BD2B@rodos.fzk.de...
> >
> > I have table xx:
> > id  debet       credit           balance
> > 1  1000        0                   0
> > 2   2000       0                   0
> > 3         0       2500             0
> > 4         0         100             0
> >
> > command in ms-sql 7 can use calculate field (column) balance from id=1
> to
> > id=4:
> > "update xx set bal=balance=bal+debet-credit"
> > result:
> > id  debet       credit           balance
> > 1  1000        0                  1000
> > 2   2000       0                   3000
> > 3         0       2500             500
> > 4         0         100             400
> >
> > How command sql can use in psotgresql 7.3?
> >
> Try
> UPDATE xx SET balance=balance+debet-credit ;
>
> Regards, Christoph
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html




Re: conversi ms-sql7 vs postgresql 7.3

From
greg@turnstep.com
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> command in ms-sql 7 can use calculate field (column) balance from id=1
> to id=4: "update xx set bal=balance=bal+debet-credit"

You cannot do such a thing in SQL alone: you must use a procedural 
language. One way is with plpgsql:

CREATE OR REPLACE FUNCTION makebalance() RETURNS VARCHAR LANGUAGE 'plpgsql' AS
'
DECLARE total  INTEGER := 0; myrow  RECORD;

BEGIN
FOR myrow IN SELECT id, credit-debit AS cd FROM xx ORDER BY id ASC LOOP total := total + myrow.cd; UPDATE xx SET
balance= total WHERE id=myrow.id;
 
END LOOP;

RETURN ''Final balance: '' || total;
END;
';

This is just a rough idea. You may have to create the language first: 
see the "createlang" script in the bin directory of your PostgreSQL installation.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302101601
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+SBN9vJuQZxSWSsgRAoNEAJ9IKmRW6IlHu12x2w7i0G6THB7oGACgi7lM
bIyssS1GQRgY0aQFRzyQKl8=
=ArPb
-----END PGP SIGNATURE-----




Re: conversi ms-sql7 vs postgresql 7.3

From
Josh Berkus
Date:
Greg,

> > command in ms-sql 7 can use calculate field (column) balance from id=1
> > to id=4: "update xx set bal=balance=bal+debet-credit"
>
> You cannot do such a thing in SQL alone: you must use a procedural
> language. One way is with plpgsql:

You're mistaken, I think (I can't find the original e-mail in this thread)

UPDATE totals SET balance = total_bal
FROM (SELECT acct_id, (sum(credit) - sum(debit)) as total_balFROM accounts GROUP BY acct_id) tb
WHERE tb.acct_id = totals.acct_idAND totals.acct_id = $selection

... would update a ficticious "totals" table with the sum of credits and
debits for a particular account.
--
-Josh BerkusAglio Database SolutionsSan Francisco