Re: sql subqueries problem

On Tue, 20 Aug 2002, Mathieu Arnold wrote:

> --On lundi 19 aot 2002 09:45 -0700 Stephan Szabo
> <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote:
>
> >
> > 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?
> >
>
> I've tried, but, as the subselect is an aggregate, I can't get it (maybe I
> don't know enough about it to do it :)

Right, that'd make it harder. :)

Hmm, would something like:

FROM
(select *,
(select sum(ff.montant_ttc/df.taux) from facture ff join
devise as df using (id_devise) where ff.date_creation <=
f.date_creation and ff.id_client=f.id_client
group by ff.id_client) as facture,
(select sum(rr.montant/dr.taux) from remise as rr join
devise as dr using (id_devise) where rr.date_paiement <=
f.date_creation and rr.id_client=f.id_client
group by rr.id_client) as remise
From facture f
);

give you something closer to what you want?