I'm trying to work around this bug:
http://sourceforge.net/tracker/index.php?func=detail&aid=1872250&group_id=175965&atid=875350
I need an AR Aging report dated august 31st 2007. Right now, the report
doesn't agree with my Balance Sheet of the same date, so there's
obviously something wrong (hence the bug report).
I have the warm fuzzy feeling, however, that I can pull the data
straight from pgsql using a clever request. Hop on board and I'll show
you what I'll try, and you'll tell me where I went wrong.
This was my first attempt at reproducing the current reports:
SELECT cu.name, SUM(amount), -SUM((SELECT SUM(amount) FROM acc_trans c
WHERE c.trans_id = a.id AND c.chart_id=10001 GROUP BY trans_id )) AS paid
FROM ar a JOIN customer cu ON (a.customer_id = cu.id)
WHERE paid <> a.amount GROUP BY cu.name ORDER BY cu.name
This lead to positive results so I tried to slap date restrictions onto
this. It's only then I noticed that the "paid" column from the "where"
above doesn't come from the SELECT, but from the ar table. Therefore if
you try to add date restrictions, you end up with the same bug as
detailed above.
So I tried changing that column name:
# select a.id, cu.name, a.invnumber, a.transdate, a.amount, (SELECT sum(c.amount) FROM acc_trans c JOIN ar a1 ON c.trans_id = a1.id WHERE c.chart_id=10004 AND c.amount > 0 AND c.transdate <= '2007-08-31' ) AS rpaid from ar a JOIN customer cu ON (a.customer_id = cu.id) where a.transdate <= '2007-08-31' HAVING rpaid <> a.amount order by cu.name;
ERROR: column "rpaid" does not exist
LINE 1: ... cu.id) where a.transdate <= '2007-08-31' HAVING rpaid <> a...
^
It is then that I obviously hit my SQL knowledge limits (rpaid *is*
defined, isn't it???).
What am I doing wrong?
Thanks for the support,
A.
--
Il n'existe aucune limite sacrÃe ou non à l'action de l'homme dans
l'univers. Depuis nos origines nous avons le choix: Ãtre aveuglà par
la vÃrità ou coudre nos paupiÃres.
- [no one is innocent]
Attachment:
signature.asc
Description: Digital signature