Re: sql subqueries problem - Mailing list pgsql-sql

--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 :)


--
Mathieu Arnold