Re: sql subqueries problem

On Tue, 20 Aug 2002, Mathieu Arnold wrote:

> --On lundi 19 ao�t 2002 09:45 -0700 Stephan Szabo
> <sszabo@megazone23.bigpanda.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_creationand rr.id_client=f.id_client    group by rr.id_client) as remiseFrom facture f 
);

give you something closer to what you want?