Re: sql subqueries problem

On Mon, 19 Aug 2002, Mathieu Arnold wrote:

> 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 ?

I don't think f is in scope on those subqueries.
Can you put the clauses on the outer where or as part of the
join conditions?