From: Mathieu Arnold <mat@mat.cc>
To: pgsql-sql@postgresql.org
Subject: sql subqueries problem
Date: Mon, 19 Aug 2002 18:15:10 +0200
Message-ID: <20595214.1029780910@andromede.reaumur.absolight.net> (raw)
Hi
I have my accounting in a database, and I have a problem with subqueries,
here is what I have :
SELECT f.numero,
f.id_client,
f.date_creation,
(f.date_creation + (f.echeance_paiement||' days')::interval)::date
AS echeance,
f.montant_ttc,
ROUND(CASE WHEN remise IS NULL THEN 0 ELSE remise END - CASE WHEN
facture IS NULL THEN 0 ELSE facture END,2) AS solde,
CASE WHEN (f.date_creation + (f.echeance_paiement||'
days')::interval)::date < 'now'::date
THEN round(f.montant_ttc * 10 / 100 * ('now'::date -
(f.date_creation + (f.echeance_paiement||' days')::interval)::date)::int /
365, 2)
ELSE NULL
END AS penalite
FROM facture AS f
JOIN (SELECT ff.id_client,
SUM(ff.montant_ttc / df.taux) AS facture
FROM facture AS ff
JOIN devise AS df USING (id_devise)
GROUP BY ff.id_client
) AS fff USING (id_client)
LEFT OUTER JOIN (SELECT rr.id_client,
SUM(rr.montant / dr.taux) AS remise
FROM remise AS rr
JOIN devise AS dr USING (id_devise)
GROUP BY rr.id_client
) AS rrr USING (id_client)
WHERE ROUND(CASE WHEN remise IS NULL THEN 0 ELSE remise END - CASE WHEN
facture IS NULL THEN 0 ELSE facture END,2) < 0
GROUP BY f.numero, f.date_creation, f.date_creation +
(f.echeance_paiement||' days')::interval, f.id_client, f.montant_ttc,
rrr.remise, fff.facture
ORDER BY f.id_client, f.numero
Table "facture"
Column | Type
-------------------+-----------------------
id_facture | integer
date_creation | date
date_modif | date
echeance_paiement | integer
id_client | integer
id_devise | integer
genere | integer
montant_ht | double precision
montant_tva | double precision
montant_ttc | double precision
solde_anterieur | double precision
total_a_payer | double precision
numero | character varying(15)
ref | character varying(60)
responsable | character varying(60)
contact | character varying(60)
num_tva | character varying(60)
adresse | text
pied | text
commentaire | text
email | text
Table "remise"
Column | Type
----------------+------------------
id_remise | integer
date_paiement | date
date_remise | date
id_client | integer
id_type_remise | integer
id_devise | integer
id_banque | integer
montant | double precision
commentaire | text
Table "devise"
Column | Type
-----------+-----------------------
id_devise | integer
taux | double precision
devise | character varying(30)
symbole | character varying(15)
It finds the invoices (facture) from my customers who forgot to pay me.
but, the probem is that it gives me all the invoices and not only the ones
which are not paid, so, I wanted to add something like :
WHERE ff.date_creation <= f.date_creation
in the first subselect, and
WHERE rr.date_paiement <= f.date_creation
in the second subselect, but I can't because postgresql does not seem to be
able to do it. Any idea ?
--
Mathieu Arnold
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: mat@mat.cc
Subject: Re: sql subqueries problem
In-Reply-To: <20595214.1029780910@andromede.reaumur.absolight.net>
* 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