Re: sql subqueries problem - Mailing list pgsql-sql

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?