Date: Mon, 19 Aug 2002 18:15:10 +0200
From: Mathieu Arnold <mat@mat.cc>
To: pgsql-sql@postgresql.org
Subject: sql subqueries problem
Message-ID: <20595214.1029780910@andromede.reaumur.absolight.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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