Thread: sql subqueries problem
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
ASdr 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 |
doubleprecision 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 |
charactervarying(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 |
charactervarying(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
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?
--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
I am importing a large number of records monthly using the \copy (from text file)command. I would like to use a sequence as a unique row ID for display in my app. Is there any way to let postgresql generate the sequence itself. Currently the only way I can make it work is to grab the next seq value and insert my own numbers into the file Thank You, -Aaron Held
On 20 Aug 2002 at 7:55, Aaron Held wrote:
> I am importing a large number of records monthly using the \copy (from
> text file)command.
>
> I would like to use a sequence as a unique row ID for display in my
> app.
>
> Is there any way to let postgresql generate the sequence itself.
> Currently the only way I can make it work is to grab the next seq
> value and insert my own numbers into the file
Yes:
create sequence mytable_id_seq;
alter table mytable alter column id set default
nextval('mycolumn_id_seq'::text);
--
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php
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?
"Aaron Held" <aaron@metrony.com> writes:
> I am importing a large number of records monthly using the \copy (from text file)command.
> I would like to use a sequence as a unique row ID for display in my app.
> Is there any way to let postgresql generate the sequence itself. Currently the only way I
> can make it work is to grab the next seq value and insert my own numbers into the file
Right now the only reasonable way to do that is to do the \copy into a
temporary table (that's missing the sequence column) and then doinsert into realtable(column list) select * from
temptable;
where the column list lists the columns you're pulling from the temp
table. The INSERT will substitute the default value (viz, nextval())
in the sequence column.
In 7.3 it'll be possible to do this in one step with no temp table:
COPY will accept a column list, so you can get the same effect just
with COPY.
regards, tom lane
>> 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