From: Joe Conway <mail@joeconway.com>
To: George McQuade <josslad@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Table conversion query...
Date: Wed, 20 Aug 2003 13:19:53 -0700
Message-ID: <3F43D7E9.2000804@joeconway.com> (raw)
In-Reply-To: <20030820193143.23054.qmail@web41611.mail.yahoo.com>
References: <20030820193143.23054.qmail@web41611.mail.yahoo.com>
George McQuade wrote:
> date tran gl amt
> 08/20/03 1001 3010 -30.00
> 08/20/03 1001 1030 -300.00
> 08/20/03 1001 1060 +330.00
> 08/20/03 1002 ...next transaction
> ...
> and I need to convert to:
>
> date glcr gldb amt
> 08/20/03 1060 3010 30.00
> 08/20/03 1060 1030 300.00
>
> in other words, the negative gl's go into gldb
> and they make up the total for the positive gl.
>
> is there a way to accomplish this in postgresql?
> or should I implement it inside the java app?
There's no simple way to do this in Postgres. You could do it with a
PL/pgSQL table function, or for better performance a C function. There
are a couple of questions yet to be answered though:
1) Can there ever be more than one credit account, e.g. -30, -300, +150,
+180?
2) What happens if sum(neg values) != sum(pos values)? Throw an error?
Joe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, josslad@yahoo.com
Subject: Re: Table conversion query...
In-Reply-To: <3F43D7E9.2000804@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox