pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsql subqueries problem
5+ messages / 2 participants
[nested] [flat]
* sql subqueries problem
@ 2002-08-19 16:15 Mathieu Arnold <mat@mat.cc>
2002-08-19 16:45 ` Re: sql subqueries problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 5+ messages in thread
From: Mathieu Arnold @ 2002-08-19 16:15 UTC (permalink / raw)
To: pgsql-sql
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 ?
--
Mathieu Arnold
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: sql subqueries problem
2002-08-19 16:15 sql subqueries problem Mathieu Arnold <mat@mat.cc>
@ 2002-08-19 16:45 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-20 06:14 ` Re: sql subqueries problem Mathieu Arnold <mat@mat.cc>
0 siblings, 1 reply; 5+ messages in thread
From: Stephan Szabo @ 2002-08-19 16:45 UTC (permalink / raw)
To: Mathieu Arnold <mat@mat.cc>; +Cc: 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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: sql subqueries problem
2002-08-19 16:15 sql subqueries problem Mathieu Arnold <mat@mat.cc>
2002-08-19 16:45 ` Re: sql subqueries problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-08-20 06:14 ` Mathieu Arnold <mat@mat.cc>
2002-08-20 14:43 ` Re: sql subqueries problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 5+ messages in thread
From: Mathieu Arnold @ 2002-08-20 06:14 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: sql subqueries problem
2002-08-19 16:15 sql subqueries problem Mathieu Arnold <mat@mat.cc>
2002-08-19 16:45 ` Re: sql subqueries problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-20 06:14 ` Re: sql subqueries problem Mathieu Arnold <mat@mat.cc>
@ 2002-08-20 14:43 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-20 15:34 ` Re: sql subqueries problem Mathieu Arnold <mat@mat.cc>
0 siblings, 1 reply; 5+ messages in thread
From: Stephan Szabo @ 2002-08-20 14:43 UTC (permalink / raw)
To: Mathieu Arnold <mat@mat.cc>; +Cc: pgsql-sql
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_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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: sql subqueries problem
2002-08-19 16:15 sql subqueries problem Mathieu Arnold <mat@mat.cc>
2002-08-19 16:45 ` Re: sql subqueries problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-20 06:14 ` Re: sql subqueries problem Mathieu Arnold <mat@mat.cc>
2002-08-20 14:43 ` Re: sql subqueries problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-08-20 15:34 ` Mathieu Arnold <mat@mat.cc>
0 siblings, 0 replies; 5+ messages in thread
From: Mathieu Arnold @ 2002-08-20 15:34 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
>> 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?
that's exactly it, I guess that I was not twisted enough for that one :)
--
Mathieu Arnold
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-08-20 15:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-19 16:15 sql subqueries problem Mathieu Arnold <mat@mat.cc>
2002-08-19 16:45 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-20 06:14 ` Mathieu Arnold <mat@mat.cc>
2002-08-20 14:43 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-20 15:34 ` Mathieu Arnold <mat@mat.cc>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox