Re: sql subqueries problem

--On lundi 19 août 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 :)

--
Mathieu Arnold