sql subqueries problem - Mailing list pgsql-sql

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
ASdr 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        |
doubleprecision      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           |
charactervarying(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    |
charactervarying(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